-- sql/schema.sql
-- Esquema inicial para el portal clientes.printcenter.digital

CREATE TABLE IF NOT EXISTS pc_orders (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    order_code          VARCHAR(50) NOT NULL UNIQUE,
    customer_name       VARCHAR(150) NOT NULL,
    customer_company    VARCHAR(150) NOT NULL,
    customer_rut        VARCHAR(20)  NOT NULL,
    customer_email      VARCHAR(150) NOT NULL,
    customer_phone      VARCHAR(50)  NULL,
    delivery_method     VARCHAR(50)  NOT NULL DEFAULT 'RETIRO_OFICINA',
    billing_doc_type    VARCHAR(20)  NOT NULL,
    delivery_notes      VARCHAR(255) NULL,
    subtotal_print      DECIMAL(12,2) NOT NULL DEFAULT 0,
    subtotal_finishing  DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_amount        DECIMAL(12,2) NOT NULL DEFAULT 0,
    currency            VARCHAR(10)  NOT NULL DEFAULT 'CLP',
    status              VARCHAR(30)  NOT NULL DEFAULT 'DRAFT',
    payment_status      VARCHAR(30)  NOT NULL DEFAULT 'NOT_REQUIRED',
    payment_provider    VARCHAR(50)  NULL,
    payment_reference   VARCHAR(100) NULL,
    onedrive_folder_id  VARCHAR(150) NULL,
    onedrive_folder_url VARCHAR(255) NULL,
    created_at          DATETIME NOT NULL,
    updated_at          DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pc_order_files (
    id                      INT AUTO_INCREMENT PRIMARY KEY,
    order_id                INT NOT NULL,
    original_name           VARCHAR(255) NOT NULL,
    stored_name             VARCHAR(255) NOT NULL,
    pages_total             INT NOT NULL DEFAULT 0,
    pages_color             INT NOT NULL DEFAULT 0,
    pages_bw                INT NOT NULL DEFAULT 0,
    size_label              VARCHAR(20)  NULL,
    size_width_mm           DECIMAL(8,2) NULL,
    size_height_mm          DECIMAL(8,2) NULL,
    copies                  INT NOT NULL DEFAULT 1,
    price_print             DECIMAL(12,2) NOT NULL DEFAULT 0,
    price_finishing         DECIMAL(12,2) NOT NULL DEFAULT 0,
    price_total             DECIMAL(12,2) NOT NULL DEFAULT 0,
    onedrive_file_id        VARCHAR(150) NULL,
    onedrive_file_url       VARCHAR(255) NULL,
    is_large_format         TINYINT(1) NOT NULL DEFAULT 0,
    color_mode_suggested    VARCHAR(30) NULL,
    color_mode_selected     VARCHAR(30) NULL,
    coverage_percent_avg    DECIMAL(5,2) NULL,
    coverage_band_suggested VARCHAR(30) NULL,
    coverage_band_selected  VARCHAR(30) NULL,
    created_at              DATETIME NOT NULL,
    updated_at              DATETIME NOT NULL,
    CONSTRAINT fk_pc_order_files_order
        FOREIGN KEY (order_id) REFERENCES pc_orders(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tablas para terminaciones (para uso futuro)
CREATE TABLE IF NOT EXISTS pc_finishings (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    description     VARCHAR(255) NULL,
    pricing_type    ENUM('PER_ORDER','PER_DOCUMENT','PER_PAGE','PER_COPY','FIXED') NOT NULL,
    price_base      DECIMAL(12,2) NOT NULL DEFAULT 0,
    price_per_unit  DECIMAL(12,2) NOT NULL DEFAULT 0,
    applies_to      ENUM('ANY','DOCS','PLANS') NOT NULL DEFAULT 'ANY',
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    created_at      DATETIME NOT NULL,
    updated_at      DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pc_order_file_finishings (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    order_file_id       INT NOT NULL,
    finishing_id        INT NOT NULL,
    quantity            INT NOT NULL DEFAULT 1,
    price_total         DECIMAL(12,2) NOT NULL DEFAULT 0,
    extra_data_json     TEXT NULL,
    created_at          DATETIME NOT NULL,
    updated_at          DATETIME NOT NULL,
    CONSTRAINT fk_pc_off_file
        FOREIGN KEY (order_file_id) REFERENCES pc_order_files(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_pc_off_finishing
        FOREIGN KEY (finishing_id) REFERENCES pc_finishings(id)
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
