CREATE DATABASE IF NOT EXISTS anglas CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE anglas;

CREATE TABLE users(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(80) NOT NULL UNIQUE,
 email VARCHAR(190) NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','user') NOT NULL DEFAULT 'user',
 status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE user_settings(
 user_id BIGINT UNSIGNED PRIMARY KEY,
 business_name VARCHAR(190),
 phone VARCHAR(50),
 logo_path VARCHAR(500),
 sunat_ruc VARCHAR(20),
 sunat_environment ENUM('beta','production') DEFAULT 'beta',
 sunat_username_enc TEXT,
 sunat_password_enc TEXT,
 sunat_connected TINYINT(1) DEFAULT 0,
 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE clients(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(190) NOT NULL, phone VARCHAR(50), dni VARCHAR(30),
 ruc VARCHAR(30), email VARCHAR(190), status VARCHAR(30),
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(user_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE projects(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NULL,
 code VARCHAR(50) NOT NULL,
 name VARCHAR(190) NOT NULL,
 description TEXT, calculator_json LONGTEXT,
 cost DECIMAL(14,2) DEFAULT 0, sale_price DECIMAL(14,2) DEFAULT 0,
 status VARCHAR(40),
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_user_code(user_id,code), INDEX(user_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE quotations(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 project_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 number VARCHAR(50), total DECIMAL(14,2) DEFAULT 0,
 status VARCHAR(40), payload_json LONGTEXT,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE sales(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 quotation_id BIGINT UNSIGNED NULL,
 client_id BIGINT UNSIGNED NULL,
 total DECIMAL(14,2) DEFAULT 0, status VARCHAR(40),
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 sale_id BIGINT UNSIGNED NULL,
 amount DECIMAL(14,2) DEFAULT 0, method VARCHAR(50), status VARCHAR(40),
 paid_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;