-- ============================================================
-- PurrCore v2 — Tablas adicionales: Roles, POS, Stock, Stripe
-- MySQL 8.0 / MariaDB 10.6+
-- ============================================================


-- ─── ROLES Y PERMISOS ─────────────────────────────────────────

-- Tabla de roles del sistema
CREATE TABLE IF NOT EXISTS roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(80) UNIQUE NOT NULL,          -- 'super_admin','admin','manager','cashier','warehouse','marketing'
    label VARCHAR(120) NOT NULL,               -- Nombre legible
    description TEXT,
    is_system TINYINT DEFAULT 0,               -- 1 = no se puede eliminar
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permisos atómicos del sistema
CREATE TABLE IF NOT EXISTS permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(120) UNIQUE NOT NULL,         -- 'orders.view','orders.refund','products.edit'…
    label VARCHAR(150) NOT NULL,
    module VARCHAR(60) NOT NULL,               -- 'orders','products','customers','finance'…
    description VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permisos asignados a cada rol
CREATE TABLE IF NOT EXISTS role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Roles asignados a staff (tabla staff separada de customers)
CREATE TABLE IF NOT EXISTS staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(30),
    role_id INT NOT NULL,
    pos_location_id INT NULL,                  -- Ubicación POS asignada (NULL = todas)
    active TINYINT DEFAULT 1,
    email_verified TINYINT DEFAULT 1,
    last_login DATETIME NULL,
    created_by INT NULL,                       -- staff.id que lo creó
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permisos extra asignados directamente a un miembro del staff (override)
CREATE TABLE IF NOT EXISTS staff_permissions (
    staff_id INT NOT NULL,
    permission_id INT NOT NULL,
    granted TINYINT DEFAULT 1,                 -- 1=concedido, 0=denegado (override deny)
    PRIMARY KEY (staff_id, permission_id),
    FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Log de acciones de staff (auditoría)
CREATE TABLE IF NOT EXISTS staff_audit_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    staff_id INT NULL,
    action VARCHAR(120) NOT NULL,
    module VARCHAR(60),
    entity_type VARCHAR(60),
    entity_id INT NULL,
    old_data JSON NULL,
    new_data JSON NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(300),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE SET NULL,
    INDEX idx_audit_staff (staff_id, created_at),
    INDEX idx_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── POS — PUNTOS DE VENTA ────────────────────────────────────

CREATE TABLE IF NOT EXISTS pos_locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    address VARCHAR(255),
    city VARCHAR(100),
    postcode VARCHAR(20),
    phone VARCHAR(30),
    email VARCHAR(150),
    stripe_terminal_location_id VARCHAR(100) NULL,  -- ID ubicación en Stripe Terminal
    active TINYINT DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Terminales POS (lectores Stripe Terminal)
CREATE TABLE IF NOT EXISTS pos_terminals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    location_id INT NOT NULL,
    label VARCHAR(100),
    stripe_reader_id VARCHAR(100) UNIQUE NULL,    -- reader_XXX de Stripe Terminal
    device_type VARCHAR(60),                       -- 'bbpos_wisepos_e','verifone_P400'…
    status ENUM('online','offline','unknown') DEFAULT 'unknown',
    last_seen DATETIME NULL,
    active TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES pos_locations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sesiones de caja POS
CREATE TABLE IF NOT EXISTS pos_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    location_id INT NOT NULL,
    terminal_id INT NULL,
    staff_id INT NOT NULL,
    status ENUM('open','closed') DEFAULT 'open',
    opening_cash DECIMAL(10,2) DEFAULT 0,
    closing_cash DECIMAL(10,2) NULL,
    expected_cash DECIMAL(10,2) NULL,
    cash_difference DECIMAL(10,2) NULL,
    total_sales DECIMAL(10,2) DEFAULT 0,
    total_orders INT DEFAULT 0,
    notes TEXT,
    opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    FOREIGN KEY (location_id) REFERENCES pos_locations(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id),
    INDEX idx_pos_sessions_status (status, opened_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── STOCK AVANZADO ───────────────────────────────────────────

-- Movimientos de stock (trazabilidad completa)
CREATE TABLE IF NOT EXISTS stock_movements (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    variant_id INT NULL,
    location_id INT NULL,                      -- NULL = almacén online/general
    type ENUM(
        'sale',          -- venta online/POS
        'return',        -- devolución
        'purchase',      -- recepción de mercancía
        'adjustment',    -- ajuste manual
        'transfer',      -- traslado entre ubicaciones
        'reserved',      -- reserva para pedido pendiente
        'unreserved',    -- liberación de reserva
        'damage',        -- merma/daño
        'initial'        -- stock inicial
    ) NOT NULL,
    quantity INT NOT NULL,                     -- positivo=entrada, negativo=salida
    quantity_before INT NOT NULL,
    quantity_after INT NOT NULL,
    unit_cost DECIMAL(10,4) NULL,             -- para cálculo de coste promedio
    reference_type VARCHAR(50) NULL,          -- 'order','pos_order','adjustment','transfer'
    reference_id INT NULL,                    -- ID del pedido/ajuste que lo generó
    staff_id INT NULL,
    notes VARCHAR(300),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL,
    FOREIGN KEY (location_id) REFERENCES pos_locations(id) ON DELETE SET NULL,
    INDEX idx_stock_product (product_id, created_at),
    INDEX idx_stock_variant (variant_id, created_at),
    INDEX idx_stock_type (type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stock por ubicación (niveles de inventario por tienda/almacén)
CREATE TABLE IF NOT EXISTS stock_levels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    variant_id INT NULL,
    location_id INT NULL,                      -- NULL = stock online/general
    quantity INT NOT NULL DEFAULT 0,
    reserved_qty INT DEFAULT 0,               -- cantidad reservada en pedidos pendientes
    min_qty INT DEFAULT 0,                    -- stock mínimo para alertas
    reorder_qty INT DEFAULT 0,               -- cantidad a pedir al llegar a mínimo
    UNIQUE KEY uq_stock_level (product_id, variant_id, location_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES pos_locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Órdenes de compra a proveedores
CREATE TABLE IF NOT EXISTS purchase_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reference VARCHAR(60) UNIQUE NOT NULL,
    supplier_name VARCHAR(150),
    supplier_email VARCHAR(200),
    status ENUM('draft','ordered','partial','received','cancelled') DEFAULT 'draft',
    subtotal DECIMAL(10,2) DEFAULT 0,
    shipping_cost DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) DEFAULT 0,
    notes TEXT,
    expected_date DATE NULL,
    received_date DATE NULL,
    location_id INT NULL,
    staff_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES pos_locations(id) ON DELETE SET NULL,
    FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchase_order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_order_id INT NOT NULL,
    product_id INT NULL,
    variant_id INT NULL,
    product_name VARCHAR(255),
    sku VARCHAR(100),
    qty_ordered INT NOT NULL,
    qty_received INT DEFAULT 0,
    unit_cost DECIMAL(10,4) NOT NULL,
    total_cost DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── STRIPE AVANZADO ──────────────────────────────────────────

-- Métodos de pago guardados (tarjetas de clientes)
CREATE TABLE IF NOT EXISTS stripe_payment_methods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    stripe_pm_id VARCHAR(100) UNIQUE NOT NULL,  -- pm_XXX
    stripe_customer_id VARCHAR(100) NOT NULL,
    type VARCHAR(30) DEFAULT 'card',
    brand VARCHAR(30),                          -- 'visa','mastercard'…
    last4 CHAR(4),
    exp_month TINYINT,
    exp_year SMALLINT,
    is_default TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_spm_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reembolsos de Stripe (trazabilidad)
CREATE TABLE IF NOT EXISTS stripe_refunds (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    invoice_id INT NULL,
    stripe_refund_id VARCHAR(100) UNIQUE NOT NULL,  -- re_XXX
    stripe_charge_id VARCHAR(100),
    amount DECIMAL(10,2) NOT NULL,
    currency CHAR(3) DEFAULT 'EUR',
    reason ENUM('duplicate','fraudulent','requested_by_customer','other') DEFAULT 'requested_by_customer',
    reason_note TEXT,
    status ENUM('pending','succeeded','failed','cancelled') DEFAULT 'pending',
    failure_reason VARCHAR(255) NULL,
    staff_id INT NULL,                              -- quién procesó el reembolso
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE SET NULL,
    INDEX idx_refunds_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- PaymentIntents de POS (Stripe Terminal)
CREATE TABLE IF NOT EXISTS pos_payment_intents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    pos_session_id INT NOT NULL,
    order_id INT NULL,
    stripe_pi_id VARCHAR(100) UNIQUE NOT NULL,
    stripe_reader_id VARCHAR(100),
    amount DECIMAL(10,2) NOT NULL,
    currency CHAR(3) DEFAULT 'EUR',
    status VARCHAR(30) DEFAULT 'requires_payment_method',
    payment_method VARCHAR(30) DEFAULT 'card_present',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pos_session_id) REFERENCES pos_sessions(id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Modificar tabla orders para POS
ALTER TABLE orders
    ADD COLUMN source ENUM('online','pos','import') DEFAULT 'online' AFTER status,
    ADD COLUMN pos_location_id INT NULL AFTER source,
    ADD COLUMN pos_session_id INT NULL AFTER pos_location_id,
    ADD COLUMN staff_id INT NULL AFTER pos_session_id,
    ADD COLUMN refunded_amount DECIMAL(10,2) DEFAULT 0 AFTER points_earned;

-- Modificar tabla staff para agregar ref al pos_location
ALTER TABLE staff
    ADD CONSTRAINT fk_staff_location FOREIGN KEY (pos_location_id) REFERENCES pos_locations(id) ON DELETE SET NULL;

-- Índices adicionales
CREATE INDEX idx_orders_source ON orders(source, created_at);
CREATE INDEX idx_orders_pos ON orders(pos_location_id, pos_session_id);
CREATE INDEX idx_staff_role ON staff(role_id, active);

