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

CREATE TABLE IF NOT EXISTS tenants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    legal_name VARCHAR(200) NULL,
    tax_id VARCHAR(80) NULL,
    fiscal_address VARCHAR(255) NULL,
    contact_email VARCHAR(190) NULL,
    contact_phone VARCHAR(60) NULL,
    logo_url VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('owner', 'admin', 'sales', 'viewer') NOT NULL DEFAULT 'viewer',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_tenant (tenant_id),
    CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    sku VARCHAR(64) NOT NULL,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    stock INT NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_products_tenant_sku (tenant_id, sku),
    KEY idx_products_tenant (tenant_id),
    CONSTRAINT fk_products_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_products_created_by FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_images (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    KEY idx_product_images_tenant_product (tenant_id, product_id),
    CONSTRAINT fk_product_images_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_product_images_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS clients (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(60) NULL,
    address VARCHAR(255) NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_clients_tenant (tenant_id),
    CONSTRAINT fk_clients_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS contracts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(180) NOT NULL,
    details TEXT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    required_products JSON NULL,
    renewal_days_notice INT NOT NULL DEFAULT 30,
    status ENUM('active', 'expired', 'cancelled') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_contracts_tenant_client (tenant_id, client_id),
    KEY idx_contracts_end_date (end_date),
    CONSTRAINT fk_contracts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_contracts_client FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reminders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    client_id BIGINT UNSIGNED NULL,
    contract_id BIGINT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    notes TEXT NULL,
    due_at DATETIME NOT NULL,
    status ENUM('pending', 'done', 'cancelled') NOT NULL DEFAULT 'pending',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_reminders_tenant_due (tenant_id, due_at),
    CONSTRAINT fk_reminders_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_reminders_client FOREIGN KEY (client_id) REFERENCES clients(id),
    CONSTRAINT fk_reminders_contract FOREIGN KEY (contract_id) REFERENCES contracts(id),
    CONSTRAINT fk_reminders_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS remisions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    folio VARCHAR(64) NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    issue_date DATE NOT NULL,
    status ENUM('draft', 'issued', 'cancelled') NOT NULL DEFAULT 'issued',
    notes TEXT NULL,
    subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
    total DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_remisions_tenant_folio (tenant_id, folio),
    KEY idx_remisions_tenant_date (tenant_id, issue_date),
    CONSTRAINT fk_remisions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_remisions_client FOREIGN KEY (client_id) REFERENCES clients(id),
    CONSTRAINT fk_remisions_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS remision_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    remision_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    line_total DECIMAL(14,2) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_remision_items_tenant_remision (tenant_id, remision_id),
    KEY idx_remision_items_product (product_id),
    CONSTRAINT fk_remision_items_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_remision_items_remision FOREIGN KEY (remision_id) REFERENCES remisions(id),
    CONSTRAINT fk_remision_items_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS stock_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    movement_type ENUM('in', 'out') NOT NULL,
    quantity INT NOT NULL,
    reference_type VARCHAR(50) NOT NULL,
    reference_id BIGINT UNSIGNED NULL,
    notes VARCHAR(255) NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    KEY idx_stock_movements_tenant_product (tenant_id, product_id),
    KEY idx_stock_movements_created_at (created_at),
    CONSTRAINT fk_stock_movements_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_stock_movements_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_stock_movements_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS menus (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NULL,
    parent_id BIGINT UNSIGNED NULL,
    title VARCHAR(120) NOT NULL,
    url VARCHAR(190) NOT NULL,
    icon VARCHAR(80) NOT NULL DEFAULT 'bx bx-circle',
    sort_order INT NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_menus_tenant_active (tenant_id, is_active),
    KEY idx_menus_parent (parent_id),
    CONSTRAINT fk_menus_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_menus_parent FOREIGN KEY (parent_id) REFERENCES menus(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS menu_role_access (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_id BIGINT UNSIGNED NOT NULL,
    role ENUM('owner', 'admin', 'sales', 'viewer') NOT NULL,
    UNIQUE KEY uq_menu_role (menu_id, role),
    CONSTRAINT fk_menu_role_menu FOREIGN KEY (menu_id) REFERENCES menus(id)
) ENGINE=InnoDB;

INSERT INTO menus (tenant_id, parent_id, title, url, icon, sort_order, is_active, created_at, updated_at)
SELECT NULL, NULL, 'Dashboard', 'hp-dashboard.html', 'bx bx-home-circle', 10, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM menus WHERE tenant_id IS NULL AND title = 'Dashboard' LIMIT 1);

INSERT INTO menus (tenant_id, parent_id, title, url, icon, sort_order, is_active, created_at, updated_at)
SELECT NULL, NULL, 'Productos', 'hp-products.html', 'bx bx-package', 20, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM menus WHERE tenant_id IS NULL AND title = 'Productos' LIMIT 1);

INSERT INTO menus (tenant_id, parent_id, title, url, icon, sort_order, is_active, created_at, updated_at)
SELECT NULL, NULL, 'Remisiones', 'hp-remisions.html', 'bx bx-receipt', 30, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM menus WHERE tenant_id IS NULL AND title = 'Remisiones' LIMIT 1);

INSERT INTO menus (tenant_id, parent_id, title, url, icon, sort_order, is_active, created_at, updated_at)
SELECT NULL, NULL, 'Empresa', 'hp-company.html', 'bx bx-building-house', 40, 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM menus WHERE tenant_id IS NULL AND title = 'Empresa' LIMIT 1);

INSERT INTO menu_role_access (menu_id, role)
SELECT m.id, roles.role_name
FROM menus m
JOIN (
    SELECT 'owner' AS role_name
    UNION ALL SELECT 'admin'
    UNION ALL SELECT 'sales'
    UNION ALL SELECT 'viewer'
) roles
WHERE m.tenant_id IS NULL
  AND m.title IN ('Dashboard', 'Productos', 'Remisiones', 'Empresa')
  AND NOT EXISTS (
      SELECT 1 FROM menu_role_access x
      WHERE x.menu_id = m.id AND x.role = roles.role_name
  );