-- ============================================================
-- SISTEMA DE GESTIÓN DE SOLICITUDES Y PRESUPUESTOS
-- Esquema de Base de Datos MySQL
-- Versión: 1.0.0
-- ============================================================


CREATE DATABASE IF NOT EXISTS `sistema_gestion`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `sistema_gestion`;

-- ============================================================
-- TABLA: roles
-- ============================================================
CREATE TABLE `roles` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(50) NOT NULL,
  `slug`        VARCHAR(50) UNIQUE NOT NULL,
  `description` TEXT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `roles` (`name`, `slug`, `description`) VALUES
  ('Administrador', 'admin',    'Acceso total al sistema'),
  ('Usuario Interno', 'internal', 'Gestiona solicitudes y presupuestos'),
  ('Cliente',       'client',   'Crea solicitudes y ve solo las suyas');

-- ============================================================
-- TABLA: clients
-- ============================================================
CREATE TABLE `clients` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(100) NOT NULL,
  `lastname`    VARCHAR(100) NOT NULL,
  `company`     VARCHAR(150) NULL,
  `email`       VARCHAR(150) NULL,
  `phone`       VARCHAR(20) NULL,
  `address`     TEXT NULL,
  `rut`         VARCHAR(20) NULL COMMENT 'RUT / Identificación fiscal',
  `notes`       TEXT NULL,
  `active`      TINYINT(1) DEFAULT 1,
  `created_by`  INT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_email`   (`email`),
  INDEX `idx_company` (`company`),
  CONSTRAINT `fk_clients_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: client_services
-- ============================================================
CREATE TABLE `client_services` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `client_id`    INT NOT NULL,
  `service_type` ENUM('hosting','domain','web_development','email','maintenance','other') NOT NULL,
  `service_name` VARCHAR(150) NOT NULL,
  `description`  TEXT NULL,
  `start_date`   DATE NULL,
  `expiry_date`  DATE NULL,
  `status`       ENUM('active','expired','suspended','pending') DEFAULT 'active',
  `price`        DECIMAL(15,2) NULL,
  `notes`        TEXT NULL,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_client_id` (`client_id`),
  CONSTRAINT `fk_cs_client` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: request_statuses
-- ============================================================
CREATE TABLE `request_statuses` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(100) NOT NULL,
  `slug`       VARCHAR(100) UNIQUE NOT NULL,
  `color`      VARCHAR(20) DEFAULT '#6b7280',
  `bg_color`   VARCHAR(20) DEFAULT '#f3f4f6',
  `parent_id`  INT NULL COMMENT 'Para subestados',
  `sort_order` INT DEFAULT 0,
  `is_active`  TINYINT(1) DEFAULT 1,
  CONSTRAINT `fk_rs_parent` FOREIGN KEY (`parent_id`) REFERENCES `request_statuses`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `request_statuses` (`id`, `name`, `slug`, `color`, `bg_color`, `parent_id`, `sort_order`) VALUES
  (1, 'Creado',                   'created',              '#1d4ed8','#dbeafe', NULL, 1),
  (2, 'Pendiente por cotización', 'pending_quotation',    '#d97706','#fef3c7', NULL, 2),
  (3, 'Cotización aprobada',      'quotation_approved',   '#059669','#d1fae5', 2,    3),
  (4, 'Pendiente por realizar',   'pending_execution',    '#7c3aed','#ede9fe', NULL, 4),
  (5, 'En curso',                 'in_progress',          '#0891b2','#cffafe', NULL, 5),
  (6, 'Terminado',                'completed',            '#16a34a','#dcfce7', NULL, 6);

-- ============================================================
-- TABLA: requests
-- ============================================================
CREATE TABLE `requests` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `title`       VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `client_id`   INT NULL,
  `assigned_to` INT NULL,
  `created_by`  INT NOT NULL,
  `status_id`   INT NOT NULL DEFAULT 1,
  `priority`    ENUM('low','medium','high','urgent') DEFAULT 'medium',
  `due_date`    DATE NULL,
  `notes`       TEXT NULL,
  `created_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_client_id`   (`client_id`),
  INDEX `idx_assigned_to` (`assigned_to`),
  INDEX `idx_created_by`  (`created_by`),
  INDEX `idx_status_id`   (`status_id`),
  CONSTRAINT `fk_req_client`   FOREIGN KEY (`client_id`)   REFERENCES `clients`(`id`)           ON DELETE SET NULL,
  CONSTRAINT `fk_req_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`)             ON DELETE SET NULL,
  CONSTRAINT `fk_req_creator`  FOREIGN KEY (`created_by`)  REFERENCES `users`(`id`),
  CONSTRAINT `fk_req_status`   FOREIGN KEY (`status_id`)   REFERENCES `request_statuses`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: request_history
-- ============================================================
CREATE TABLE `request_history` (
  `id`         INT AUTO_INCREMENT PRIMARY KEY,
  `request_id` INT NOT NULL,
  `user_id`    INT NOT NULL,
  `action`     VARCHAR(100) NOT NULL,
  `old_value`  TEXT NULL,
  `new_value`  TEXT NULL,
  `comment`    TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_request_id` (`request_id`),
  CONSTRAINT `fk_rh_request` FOREIGN KEY (`request_id`) REFERENCES `requests`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rh_user`    FOREIGN KEY (`user_id`)    REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: quotations
-- ============================================================
CREATE TABLE `quotations` (
  `id`               INT AUTO_INCREMENT PRIMARY KEY,
  `number`           VARCHAR(50) UNIQUE NOT NULL COMMENT 'Ej: COT-2024-0001',
  `request_id`       INT NULL,
  `client_id`        INT NULL,
  `client_name`      VARCHAR(100) NOT NULL,
  `client_lastname`  VARCHAR(100) NOT NULL,
  `client_email`     VARCHAR(150) NULL,
  `client_phone`     VARCHAR(20) NULL,
  `client_address`   TEXT NULL,
  `client_rut`       VARCHAR(20) NULL,
  `subtotal`         DECIMAL(15,2) DEFAULT 0.00,
  `tax_rate`         DECIMAL(5,2) DEFAULT 19.00 COMMENT 'IVA en %',
  `tax_amount`       DECIMAL(15,2) DEFAULT 0.00,
  `discount`         DECIMAL(15,2) DEFAULT 0.00,
  `total`            DECIMAL(15,2) DEFAULT 0.00,
  `apply_tax`        TINYINT(1) DEFAULT 1,
  `currency`         VARCHAR(10) DEFAULT 'CLP',
  `notes`            TEXT NULL,
  `terms`            TEXT NULL,
  `status`           ENUM('draft','sent','approved','rejected','expired') DEFAULT 'draft',
  `valid_until`      DATE NULL,
  `pdf_path`         VARCHAR(255) NULL,
  `created_by`       INT NOT NULL,
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_request_id` (`request_id`),
  INDEX `idx_client_id`  (`client_id`),
  INDEX `idx_status`     (`status`),
  CONSTRAINT `fk_quot_request` FOREIGN KEY (`request_id`) REFERENCES `requests`(`id`)  ON DELETE SET NULL,
  CONSTRAINT `fk_quot_client`  FOREIGN KEY (`client_id`)  REFERENCES `clients`(`id`)   ON DELETE SET NULL,
  CONSTRAINT `fk_quot_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: quotation_items
-- ============================================================
CREATE TABLE `quotation_items` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `quotation_id` INT NOT NULL,
  `product`      VARCHAR(255) NOT NULL,
  `description`  TEXT NULL,
  `quantity`     DECIMAL(10,2) DEFAULT 1.00,
  `unit_price`   DECIMAL(15,2) NOT NULL,
  `subtotal`     DECIMAL(15,2) NOT NULL,
  `sort_order`   INT DEFAULT 0,
  INDEX `idx_quotation_id` (`quotation_id`),
  CONSTRAINT `fk_qi_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: attachments
-- ============================================================
CREATE TABLE `attachments` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `request_id`    INT NULL,
  `quotation_id`  INT NULL,
  `user_id`       INT NOT NULL,
  `filename`      VARCHAR(255) NOT NULL COMMENT 'Nombre en disco',
  `original_name` VARCHAR(255) NOT NULL COMMENT 'Nombre original',
  `file_type`     VARCHAR(100) NOT NULL,
  `file_size`     INT NOT NULL COMMENT 'Tamaño en bytes',
  `file_path`     VARCHAR(500) NOT NULL,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_request_id`   (`request_id`),
  INDEX `idx_quotation_id` (`quotation_id`),
  CONSTRAINT `fk_att_request`   FOREIGN KEY (`request_id`)   REFERENCES `requests`(`id`)   ON DELETE CASCADE,
  CONSTRAINT `fk_att_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_att_user`      FOREIGN KEY (`user_id`)      REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: system_settings
-- ============================================================
CREATE TABLE `system_settings` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) UNIQUE NOT NULL,
  `value`       TEXT NULL,
  `type`        ENUM('string','number','boolean','json','file') DEFAULT 'string',
  `group`       VARCHAR(50) DEFAULT 'general',
  `label`       VARCHAR(150) NULL,
  `updated_at`  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `system_settings` (`setting_key`, `value`, `type`, `group`, `label`) VALUES
  ('company_name',      'Mi Empresa',             'string', 'company', 'Nombre de la empresa'),
  ('company_address',   'Dirección de la empresa','string', 'company', 'Dirección'),
  ('company_phone',     '+56 9 0000 0000',        'string', 'company', 'Teléfono'),
  ('company_email',     'contacto@empresa.com',   'string', 'company', 'Email'),
  ('company_rut',       '00.000.000-0',           'string', 'company', 'RUT empresa'),
  ('company_website',   'www.empresa.com',        'string', 'company', 'Sitio web'),
  ('company_logo',      '',                       'file',   'company', 'Logo empresa'),
  ('pdf_primary_color', '#1d4ed8',                'string', 'pdf',     'Color primario PDF'),
  ('pdf_footer_text',   'Gracias por su confianza','string','pdf',    'Pie de página PDF'),
  ('pdf_terms',         'Los presupuestos tienen validez de 30 días.', 'string', 'pdf', 'Términos y condiciones'),
  ('tax_rate',          '19',                     'number', 'billing', 'Tasa de impuesto (%)'),
  ('currency',          'CLP',                    'string', 'billing', 'Moneda'),
  ('quotation_prefix',  'COT',                    'string', 'billing', 'Prefijo cotización'),
  ('mail_from_name',    'Sistema',                'string', 'mail',    'Nombre remitente'),
  ('mail_from_email',   'noreply@empresa.com',    'string', 'mail',    'Email remitente');
