CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 role ENUM('customer','staff','admin') NOT NULL DEFAULT 'customer',
 first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE, phone VARCHAR(30) NULL,
 password_hash VARCHAR(255) NOT NULL,
 status ENUM('pending','active','suspended','deleted') NOT NULL DEFAULT 'pending',
 email_verified_at DATETIME NULL, marketing_consent TINYINT(1) NOT NULL DEFAULT 0,
 privacy_consent_at DATETIME NULL, last_login_at DATETIME NULL,
 twofa_enabled TINYINT(1) NOT NULL DEFAULT 0, twofa_secret TEXT NULL, twofa_backup_codes TEXT NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE verification_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE, type ENUM('email_verify','password_reset') NOT NULL,
 expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX(type,expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE packages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, description TEXT,
 speed_mbps INT UNSIGNED NOT NULL, price_cents INT UNSIGNED NOT NULL, billing_cycle ENUM('monthly','once') NOT NULL DEFAULT 'monthly', active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE coverage_areas (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL UNIQUE, province VARCHAR(80) NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE applications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
 package_id BIGINT UNSIGNED NULL, coverage_area_id BIGINT UNSIGNED NULL, service_address TEXT NOT NULL, suburb VARCHAR(120), town VARCHAR(120), province VARCHAR(80) DEFAULT 'KwaZulu-Natal', postal_code VARCHAR(20), latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL,
 status ENUM('submitted','checking','approved','scheduled','installed','rejected','cancelled') NOT NULL DEFAULT 'submitted', notes TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(package_id) REFERENCES packages(id) ON DELETE SET NULL, FOREIGN KEY(coverage_area_id) REFERENCES coverage_areas(id) ON DELETE SET NULL, INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE invoices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, application_id BIGINT UNSIGNED NULL, invoice_no VARCHAR(40) NOT NULL UNIQUE,
 description VARCHAR(255) NOT NULL, amount_cents INT UNSIGNED NOT NULL, due_date DATE NOT NULL, billing_period CHAR(7) NULL, status ENUM('unpaid','paid','void','overdue') NOT NULL DEFAULT 'unpaid', paid_at DATETIME NULL, created_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE SET NULL, INDEX(user_id,status), UNIQUE KEY uq_user_period (user_id,billing_period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL,
 gateway VARCHAR(40) NOT NULL, reference VARCHAR(120) NULL, amount_cents INT UNSIGNED NOT NULL, status ENUM('pending','paid','failed','cancelled') NOT NULL DEFAULT 'pending', gateway_payload JSON NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX(reference), UNIQUE KEY uq_gateway_reference(gateway,reference), INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE tickets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, subject VARCHAR(180) NOT NULL, priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', status ENUM('open','pending','resolved','closed') NOT NULL DEFAULT 'open', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE ticket_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ticket_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, message TEXT NOT NULL, created_at DATETIME NOT NULL,
 FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL, ip_address VARCHAR(45), user_agent VARCHAR(500), metadata JSON NULL, created_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX(action,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE privacy_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, type ENUM('access','correction','deletion','objection','marketing_optout') NOT NULL, details TEXT, status ENUM('received','processing','completed','rejected') NOT NULL DEFAULT 'received', created_at DATETIME NOT NULL, completed_at DATETIME NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE login_attempts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NULL, ip_address VARCHAR(45) NOT NULL, success TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, INDEX(ip_address,created_at), INDEX(email,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO packages(name,description,speed_mbps,price_cents,billing_cycle,active,created_at,updated_at) VALUES
('Lite','Up to 5 Mbps',5,19900,'monthly',1,NOW(),NOW()),('Smart','Up to 10 Mbps',10,29900,'monthly',1,NOW(),NOW()),('Ultra','20–30 Mbps',30,59900,'monthly',1,NOW(),NOW()),('Fibre Essential','Up to 20 Mbps',20,34900,'monthly',1,NOW(),NOW()),('Fibre Plus','Up to 50 Mbps',50,60200,'monthly',1,NOW(),NOW()),('Corporate','Business Connectivity',50,105000,'monthly',1,NOW(),NOW());
INSERT INTO coverage_areas(name,province,active,created_at) VALUES
('Ladysmith','KwaZulu-Natal',1,NOW()),('Ezakheni','KwaZulu-Natal',1,NOW()),('Wasbank','KwaZulu-Natal',1,NOW()),('Dundee','KwaZulu-Natal',1,NOW()),('Glencoe','KwaZulu-Natal',1,NOW()),('Madadeni','KwaZulu-Natal',1,NOW()),('Estcourt','KwaZulu-Natal',1,NOW()),('Uitval','KwaZulu-Natal',1,NOW()),('Nquthu','KwaZulu-Natal',1,NOW()),('Gauteng','Gauteng',1,NOW()),('Soweto','Gauteng',1,NOW());