CREATE DATABASE IF NOT EXISTS service_hub CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE service_hub;
CREATE TABLE roles (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL UNIQUE,description VARCHAR(255) NULL,created_at DATETIME NULL,updated_at DATETIME NULL) ENGINE=InnoDB;
CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,role_id INT UNSIGNED NOT NULL,first_name VARCHAR(120) NOT NULL,last_name VARCHAR(120) NOT NULL,age TINYINT UNSIGNED NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',accepted_terms_at DATETIME NULL,created_at DATETIME NULL,updated_at DATETIME NULL,CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)) ENGINE=InnoDB;
CREATE TABLE api_tokens (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,name VARCHAR(120) NOT NULL,token CHAR(64) NOT NULL UNIQUE,last_used_at DATETIME NULL,expires_at DATETIME NULL,revoked_at DATETIME NULL,created_at DATETIME NULL,CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users(id),INDEX idx_tokens_user (user_id),INDEX idx_tokens_expiry (expires_at)) ENGINE=InnoDB;
CREATE TABLE password_resets (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) NOT NULL,token CHAR(64) NOT NULL,expires_at DATETIME NOT NULL,used_at DATETIME NULL,created_at DATETIME NULL,INDEX idx_resets_email (email),INDEX idx_resets_token (token)) ENGINE=InnoDB;
CREATE TABLE settings (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,`key` VARCHAR(120) NOT NULL UNIQUE,`value` TEXT NOT NULL,group_name VARCHAR(80) NOT NULL DEFAULT 'general',created_at DATETIME NULL,updated_at DATETIME NULL) ENGINE=InnoDB;
CREATE TABLE categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL UNIQUE,description TEXT NULL,kind ENUM('recurrent','unique','rental','service','product') NOT NULL,status ENUM('active','inactive') NOT NULL DEFAULT 'active',created_at DATETIME NULL,updated_at DATETIME NULL) ENGINE=InnoDB;
CREATE TABLE products (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,category_id INT UNSIGNED NOT NULL,name VARCHAR(160) NOT NULL,slug VARCHAR(180) NOT NULL UNIQUE,description TEXT NULL,image_url VARCHAR(255) NULL,base_price DECIMAL(12,2) NOT NULL DEFAULT 0,billing_type ENUM('unique','monthly','annual','recurrent','weekly','custom') NOT NULL,stock_quantity INT NULL,service_rules TEXT NULL,requires_credentials TINYINT(1) NOT NULL DEFAULT 0,requires_renewal TINYINT(1) NOT NULL DEFAULT 0,default_duration_days INT NULL,benefits TEXT NULL,restrictions TEXT NULL,status ENUM('active','inactive','draft') NOT NULL DEFAULT 'active',created_at DATETIME NULL,updated_at DATETIME NULL,CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id),INDEX idx_products_category (category_id),INDEX idx_products_status (status)) ENGINE=InnoDB;
CREATE TABLE product_plans (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id INT UNSIGNED NOT NULL,name VARCHAR(120) NOT NULL,description TEXT NULL,billing_cycle ENUM('unique','monthly','annual','weekly','custom') NOT NULL,duration_days INT NULL,price DECIMAL(12,2) NOT NULL,allow_quantity TINYINT(1) NOT NULL DEFAULT 0,min_quantity INT NOT NULL DEFAULT 1,max_quantity INT NULL,status ENUM('active','inactive') NOT NULL DEFAULT 'active',created_at DATETIME NULL,updated_at DATETIME NULL,CONSTRAINT fk_plans_product FOREIGN KEY (product_id) REFERENCES products(id),INDEX idx_plans_product (product_id)) ENGINE=InnoDB;
CREATE TABLE coupons (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(60) NOT NULL UNIQUE,name VARCHAR(120) NOT NULL,discount_type ENUM('percent','fixed') NOT NULL,discount_value DECIMAL(12,2) NOT NULL,usage_limit INT NULL,used_count INT NOT NULL DEFAULT 0,starts_at DATETIME NULL,ends_at DATETIME NULL,status ENUM('active','inactive') NOT NULL DEFAULT 'active',created_at DATETIME NULL,updated_at DATETIME NULL) ENGINE=InnoDB;
CREATE TABLE orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(40) NOT NULL UNIQUE,user_id INT UNSIGNED NOT NULL,status ENUM('pending_payment','payment_submitted','paid','payment_rejected','cancelled','completed') NOT NULL DEFAULT 'pending_payment',subtotal DECIMAL(12,2) NOT NULL,discount_total DECIMAL(12,2) NOT NULL DEFAULT 0,total DECIMAL(12,2) NOT NULL,notes TEXT NULL,created_at DATETIME NULL,updated_at DATETIME NULL,CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),INDEX idx_orders_user (user_id),INDEX idx_orders_status (status)) ENGINE=InnoDB;
CREATE TABLE order_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id BIGINT UNSIGNED NOT NULL,product_id INT UNSIGNED NOT NULL,plan_id INT UNSIGNED NOT NULL,description VARCHAR(255) NOT NULL,quantity INT NOT NULL DEFAULT 1,unit_price DECIMAL(12,2) NOT NULL,discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,total_price DECIMAL(12,2) NOT NULL,starts_at DATETIME NULL,ends_at DATETIME NULL,service_status ENUM('pending','active','expiring','expired','renewed','suspended') NOT NULL DEFAULT 'pending',created_at DATETIME NULL,updated_at DATETIME NULL,CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id),CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id),CONSTRAINT fk_order_items_plan FOREIGN KEY (plan_id) REFERENCES product_plans(id),INDEX idx_order_items_order (order_id),INDEX idx_order_items_end (ends_at)) ENGINE=InnoDB;
CREATE TABLE payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id BIGINT UNSIGNED NOT NULL,user_id INT UNSIGNED NOT NULL,payment_method VARCHAR(60) NOT NULL DEFAULT 'manual',amount DECIMAL(12,2) NOT NULL,status ENUM('pending','under_review','approved','rejected') NOT NULL DEFAULT 'pending',notes TEXT NULL,review_notes TEXT NULL,reviewed_by INT UNSIGNED NULL,reviewed_at DATETIME NULL,created_at DATETIME NULL,updated_at DATETIME NULL,CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id),CONSTRAINT fk_payments_user FOREIGN KEY (user_id) REFERENCES users(id),CONSTRAINT fk_payments_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id),INDEX idx_payments_order (order_id),INDEX idx_payments_status (status)) ENGINE=InnoDB;
CREATE TABLE vouchers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,payment_id BIGINT UNSIGNED NOT NULL,order_id BIGINT UNSIGNED NOT NULL,file_name VARCHAR(255) NOT NULL,file_path VARCHAR(255) NOT NULL,mime_type VARCHAR(100) NOT NULL,file_size BIGINT NOT NULL,status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',uploaded_at DATETIME NULL,CONSTRAINT fk_vouchers_payment FOREIGN KEY (payment_id) REFERENCES payments(id),CONSTRAINT fk_vouchers_order FOREIGN KEY (order_id) REFERENCES orders(id)) ENGINE=InnoDB;
CREATE TABLE service_credentials (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,order_id BIGINT UNSIGNED NOT NULL,access_username VARCHAR(190) NOT NULL,access_password VARCHAR(190) NOT NULL,pin_code VARCHAR(80) NULL,profile_name VARCHAR(120) NULL,access_url VARCHAR(255) NULL,notes TEXT NULL,sent_by INT UNSIGNED NOT NULL,sent_at DATETIME NULL,created_at DATETIME NULL,updated_at DATETIME NULL,CONSTRAINT fk_credentials_user FOREIGN KEY (user_id) REFERENCES users(id),CONSTRAINT fk_credentials_order FOREIGN KEY (order_id) REFERENCES orders(id),CONSTRAINT fk_credentials_sender FOREIGN KEY (sent_by) REFERENCES users(id),INDEX idx_credentials_user (user_id)) ENGINE=InnoDB;
CREATE TABLE notifications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,title VARCHAR(160) NOT NULL,message TEXT NOT NULL,type ENUM('info','success','warning','danger') NOT NULL DEFAULT 'info',is_read TINYINT(1) NOT NULL DEFAULT 0,read_at DATETIME NULL,created_at DATETIME NULL,CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id),INDEX idx_notifications_user (user_id, is_read)) ENGINE=InnoDB;
CREATE TABLE accounts_receivable (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,order_id BIGINT UNSIGNED NOT NULL,due_date DATE NOT NULL,amount DECIMAL(12,2) NOT NULL,balance DECIMAL(12,2) NOT NULL,status ENUM('pending','partial','paid','overdue') NOT NULL DEFAULT 'pending',created_at DATETIME NULL,updated_at DATETIME NULL,CONSTRAINT fk_ar_user FOREIGN KEY (user_id) REFERENCES users(id),CONSTRAINT fk_ar_order FOREIGN KEY (order_id) REFERENCES orders(id),INDEX idx_ar_due (due_date, status)) ENGINE=InnoDB;
CREATE TABLE accounts_payable (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,category VARCHAR(120) NOT NULL,provider_name VARCHAR(160) NOT NULL,due_date DATE NOT NULL,amount DECIMAL(12,2) NOT NULL,balance DECIMAL(12,2) NOT NULL,status ENUM('pending','partial','paid','overdue') NOT NULL DEFAULT 'pending',notes TEXT NULL,created_at DATETIME NULL,updated_at DATETIME NULL,INDEX idx_ap_due (due_date, status)) ENGINE=InnoDB;
CREATE TABLE audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NULL,action VARCHAR(80) NOT NULL,entity VARCHAR(80) NOT NULL,entity_id BIGINT NULL,payload_json JSON NULL,ip_address VARCHAR(60) NULL,created_at DATETIME NULL,CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id)) ENGINE=InnoDB;
INSERT INTO roles (id, name, description, created_at, updated_at) VALUES (1,'admin','Administrador del sistema',NOW(),NOW()),(2,'customer','Cliente o consumidor',NOW(),NOW());
INSERT INTO users (role_id, first_name, last_name, age, email, password_hash, status, accepted_terms_at, created_at, updated_at) VALUES (1,'Admin','Principal',30,'admin@servicehub.test','$2y$10$mLVn0T65nbY3bQWJDb7FvOOWQHd4.zwcs0t.bMFMZqv/8G7dLxCJ2','active',NOW(),NOW(),NOW());
INSERT INTO categories (name, description, kind, status, created_at, updated_at) VALUES ('Streaming','Servicios de entretenimiento mensual','recurrent','active',NOW(),NOW()),('Licencias permanentes','Licencias de software de pago único','unique','active',NOW(),NOW()),('Alquileres','Habitaciones, casas y departamentos','rental','active',NOW(),NOW()),('Servicios básicos','Agua, luz y otros','service','active',NOW(),NOW());
INSERT INTO products (category_id, name, slug, description, image_url, base_price, billing_type, stock_quantity, service_rules, requires_credentials, requires_renewal, default_duration_days, benefits, restrictions, status, created_at, updated_at) VALUES (1,'Netflix Premium','netflix-premium','Cuenta compartida con perfil personalizado',NULL,35.00,'monthly',NULL,'No cambiar correo ni contraseña',1,1,30,'HD, múltiples pantallas','Uso responsable','active',NOW(),NOW()),(2,'Microsoft Office 2021 Pro','microsoft-office-2021-pro','Licencia permanente para Windows',NULL,120.00,'unique',10,'Activación en 1 equipo',1,0,3650,'Activación permanente','No redistribuir','active',NOW(),NOW()),(3,'Alquiler Habitación Simple','alquiler-habitacion-simple','Habitación en zona céntrica',NULL,450.00,'monthly',2,'Pago adelantado mensual',0,1,30,'Servicios compartidos','No mascotas','active',NOW(),NOW());
INSERT INTO product_plans (product_id, name, description, billing_cycle, duration_days, price, allow_quantity, min_quantity, max_quantity, status, created_at, updated_at) VALUES (1,'Mensual','Plan mensual','monthly',30,35.00,0,1,1,'active',NOW(),NOW()),(2,'Pago único','Licencia permanente','unique',3650,120.00,1,1,5,'active',NOW(),NOW()),(3,'Mensual','Pago de alquiler por 30 días','monthly',30,450.00,1,1,12,'active',NOW(),NOW());
INSERT INTO coupons (code, name, discount_type, discount_value, usage_limit, used_count, starts_at, ends_at, status, created_at, updated_at) VALUES ('BIENVENIDA10','Cupón bienvenida','percent',10,100,0,NOW(),DATE_ADD(NOW(), INTERVAL 90 DAY),'active',NOW(),NOW());
INSERT INTO settings (`key`,`value`,group_name,created_at,updated_at) VALUES ('company_name','Service Hub','general',NOW(),NOW()),('payment_methods','Yape, Plin, Transferencia','payments',NOW(),NOW()),('terms_text','Acepto los términos y condiciones del servicio.','legal',NOW(),NOW()),('expiring_notice_days','5','notifications',NOW(),NOW());