-- Full schema with plans seed
CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, company VARCHAR(160), email VARCHAR(160), phone_company VARCHAR(30), phone_personal VARCHAR(30), phone_whatsapp VARCHAR(30), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS plans (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL, web_price INT NOT NULL, hosting_price INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS orders (id INT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(40) UNIQUE, customer_id INT NOT NULL, website_type VARCHAR(60), plan_id INT, domain_name VARCHAR(160), hosting_plan VARCHAR(80), full_price INT DEFAULT 0, advance_paid INT DEFAULT 0, delivery_date DATE, status ENUM('New','Design','Dev','QA','Delivered','On Hold','Dead') DEFAULT 'New', note TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, FOREIGN KEY (plan_id) REFERENCES plans(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payments (id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, amount INT NOT NULL, method VARCHAR(40), paid_on DATE, note VARCHAR(160), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(120) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO plans (name, web_price, hosting_price) VALUES ('Starter Plan',15000,8000),('Regular Plan',25000,12000),('Standard Plan',35000,18000),('Premium Plan',65000,35000) ON DUPLICATE KEY UPDATE web_price=VALUES(web_price), hosting_price=VALUES(hosting_price);