-- ============================================================
-- ZLETS - Sistema de Control de Letras para JW DIESEL S.A.C.
-- Base de datos: zlets_db
-- Motor: MySQL / MariaDB
-- Charset: UTF-8
-- ============================================================

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

-- ============================================================
-- TABLA: empresas (proveedores de JW DIESEL)
-- ============================================================
DROP TABLE IF EXISTS letras;
DROP TABLE IF EXISTS grupos_letras;
DROP TABLE IF EXISTS empresas;

CREATE TABLE empresas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    razon_social VARCHAR(200) NOT NULL,
    ruc VARCHAR(20) DEFAULT NULL,
    direccion VARCHAR(300) DEFAULT NULL,
    telefono VARCHAR(20) DEFAULT NULL,
    contacto VARCHAR(150) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    estado TINYINT DEFAULT 1 COMMENT '1=Activo, 0=Inactivo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: grupos_letras (cada factura canjeada por letras)
-- ============================================================
CREATE TABLE grupos_letras (
    id INT AUTO_INCREMENT PRIMARY KEY,
    empresa_id INT NOT NULL,
    serie VARCHAR(10) DEFAULT '001',
    documento_sunat VARCHAR(50) DEFAULT NULL,
    fecha_emision_factura DATE DEFAULT NULL,
    moneda ENUM('USD','PEN') DEFAULT 'USD',
    importe_factura DECIMAL(12,2) DEFAULT 0.00,
    total_letras DECIMAL(12,2) DEFAULT 0.00,
    cantidad_letras INT DEFAULT 0,
    observaciones TEXT DEFAULT NULL,
    estado TINYINT DEFAULT 1 COMMENT '1=Activo, 0=Anulado',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: letras (cada letra individual)
-- ============================================================
CREATE TABLE letras (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grupo_id INT NOT NULL,
    empresa_id INT NOT NULL,
    numero_letra VARCHAR(50) NOT NULL,
    numero_en_grupo INT DEFAULT 1,
    renovacion INT DEFAULT 0,
    tipo_letra ENUM('ORI','REN') DEFAULT 'ORI' COMMENT 'ORI=Original, REN=Renovada',
    moneda ENUM('USD','PEN') DEFAULT 'USD',
    fecha_emision DATE NOT NULL,
    plazo_dias INT DEFAULT 30,
    fecha_vencimiento DATE NOT NULL,
    fecha_pago DATE GENERATED ALWAYS AS (DATE_ADD(fecha_vencimiento, INTERVAL 8 DAY)) STORED,
    importe DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    banco VARCHAR(100) DEFAULT NULL,
    estado ENUM('pendiente','pagada','vencida','renovada','protestada') DEFAULT 'pendiente',
    referencia_unica VARCHAR(100) DEFAULT NULL,
    observaciones TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (grupo_id) REFERENCES grupos_letras(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_fecha_vencimiento (fecha_vencimiento),
    INDEX idx_fecha_pago (fecha_pago),
    INDEX idx_estado (estado),
    INDEX idx_empresa_id (empresa_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- DATOS DE PRUEBA
-- ============================================================

-- Empresas proveedoras
INSERT INTO empresas (razon_social, ruc, direccion, telefono) VALUES
('DITERSA', '20123456781', 'Av. Industrial 456, Lima', '01-4567890'),
('JYW REPUESTOS S.R.L.', '20456789012', 'Tienda La Victoria, Lima', '944922651'),
('RENUSA', '20789012345', 'Calle Los Olivos 123, Lima', '01-7890123'),
('MAQUINARIAS DEL NORTE S.A.C.', '20112233445', 'Jr. Comercio 789, Chimbote', '043-321456'),
('IMPORTACIONES DIESEL PERU E.I.R.L.', '20556677889', 'Av. Argentina 1500, Callao', '01-5566778');

-- ============================================================
-- Grupo 1: DITERSA - 1 letra
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (1, '001', '2084046', '2026-04-22', 'USD', 398.00, 398.00, 1);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(1, 1, '3061216', 1, 0, 'ORI', 'USD', '2026-04-24', 61, '2026-06-22', 398.00, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 2: JYW REPUESTOS S.R.L. - 13 letras (de letra.jpeg)
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (2, '001', '000000000012884', '2026-05-09', 'USD', 75.00, 4948.68, 13);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(2, 2, '01684 2026', 1, 0, 'ORI', 'USD', '2026-05-14', 30, '2026-06-13', 380.67, NULL, 'pagada', NULL),
(2, 2, '01685 2026', 2, 0, 'ORI', 'USD', '2026-05-14', 35, '2026-06-18', 380.67, NULL, 'pagada', NULL),
(2, 2, '01686 2026', 3, 0, 'ORI', 'USD', '2026-05-14', 40, '2026-06-23', 380.67, NULL, 'pagada', NULL),
(2, 2, '01687 2026', 4, 0, 'ORI', 'USD', '2026-05-14', 45, '2026-06-28', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01688 2026', 5, 0, 'ORI', 'USD', '2026-05-14', 50, '2026-07-03', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01689 2026', 6, 0, 'ORI', 'USD', '2026-05-14', 55, '2026-07-08', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01690 2026', 7, 0, 'ORI', 'USD', '2026-05-14', 60, '2026-07-13', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01691 2026', 8, 0, 'ORI', 'USD', '2026-05-14', 65, '2026-07-18', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01692 2026', 9, 0, 'ORI', 'USD', '2026-05-14', 70, '2026-07-23', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01693 2026', 10, 0, 'ORI', 'USD', '2026-05-14', 75, '2026-07-28', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01694 2026', 11, 0, 'ORI', 'USD', '2026-05-14', 80, '2026-08-02', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01695 2026', 12, 0, 'ORI', 'USD', '2026-05-14', 85, '2026-08-07', 380.67, NULL, 'pendiente', NULL),
(2, 2, '01696 2026', 13, 0, 'ORI', 'USD', '2026-05-14', 90, '2026-08-12', 380.64, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 3: JYW REPUESTOS S.R.L. - Otra factura (de reporte.png)
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (2, '001', '00807-1', '2026-05-22', 'USD', 180.93, 180.93, 1);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(3, 2, '3051603', 1, 0, 'ORI', 'USD', '2026-06-01', 30, '2026-06-21', 180.93, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 4: JYW REPUESTOS - letras adicionales (reporte.png)
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (2, '003', '01170', '2026-03-03', 'USD', 397.62, 397.62, 1);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(4, 2, '3953597', 1, 0, 'ORI', 'USD', '2026-05-08', 110, '2026-06-21', 397.62, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 5: JYW REPUESTOS - letra 01686
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (2, '001', '01686', '2026-05-14', 'USD', 380.67, 380.67, 1);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(5, 2, '3061606', 1, 0, 'ORI', 'USD', '2026-06-21', 40, '2026-06-23', 380.67, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 6: JYW REPUESTOS - letra 01163-1
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (2, '001', '01163-1', '2026-05-24', 'USD', 198.81, 198.81, 1);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(6, 2, '3051603', 1, 0, 'ORI', 'USD', '2026-06-23', 30, '2026-07-01', 198.81, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 7: JYW REPUESTOS - letra 01347-1
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (2, '001', '01347-1', '2026-05-26', 'USD', 173.08, 173.08, 1);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(7, 2, '3051603', 1, 0, 'ORI', 'USD', '2026-06-25', 30, '2026-07-03', 173.08, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 8: JYW REPUESTOS - letra 01171
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (2, '003', '01171', '2026-03-03', 'USD', 397.62, 397.62, 1);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(8, 2, '3953597', 1, 0, 'ORI', 'USD', '2026-05-08', 114, '2026-07-03', 397.62, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 9: RENUSA - 2 letras (de reporte.png)
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (3, '001', '540071351/00', '2026-06-16', 'USD', 774.38, 774.38, 2);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(9, 3, '3661367', 1, 0, 'ORI', 'USD', '2026-06-30', 6, '2026-06-22', 433.81, NULL, 'pendiente', NULL),
(9, 3, '3661366', 2, 0, 'ORI', 'USD', '2026-06-25', 79, '2026-07-03', 340.57, NULL, 'pendiente', NULL);

-- ============================================================
-- Grupo 10: MAQUINARIAS DEL NORTE - datos adicionales
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (4, '001', '0045678', '2026-04-15', 'USD', 2500.00, 2500.00, 5);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(10, 4, 'MN-001-2026', 1, 0, 'ORI', 'USD', '2026-04-15', 30, '2026-05-15', 500.00, 'CONTINENTAL', 'pagada', NULL),
(10, 4, 'MN-002-2026', 2, 0, 'ORI', 'USD', '2026-04-15', 60, '2026-06-14', 500.00, 'CONTINENTAL', 'pagada', NULL),
(10, 4, 'MN-003-2026', 3, 0, 'ORI', 'USD', '2026-04-15', 90, '2026-07-14', 500.00, 'INTERBANK', 'pendiente', NULL),
(10, 4, 'MN-004-2026', 4, 0, 'ORI', 'USD', '2026-04-15', 120, '2026-08-13', 500.00, 'INTERBANK', 'pendiente', NULL),
(10, 4, 'MN-005-2026', 5, 0, 'ORI', 'USD', '2026-04-15', 150, '2026-09-12', 500.00, 'SCOTIABANK', 'pendiente', NULL);

-- ============================================================
-- Grupo 11: IMPORTACIONES DIESEL PERU
-- ============================================================
INSERT INTO grupos_letras (empresa_id, serie, documento_sunat, fecha_emision_factura, moneda, importe_factura, total_letras, cantidad_letras)
VALUES (5, '002', '0098765', '2026-05-01', 'USD', 1800.00, 1800.00, 3);

INSERT INTO letras (grupo_id, empresa_id, numero_letra, numero_en_grupo, renovacion, tipo_letra, moneda, fecha_emision, plazo_dias, fecha_vencimiento, importe, banco, estado, referencia_unica) VALUES
(11, 5, 'IDP-001-2026', 1, 0, 'ORI', 'USD', '2026-05-01', 30, '2026-05-31', 600.00, 'PICHINCHA', 'pagada', NULL),
(11, 5, 'IDP-002-2026', 2, 0, 'ORI', 'USD', '2026-05-01', 60, '2026-06-30', 600.00, 'PICHINCHA', 'pendiente', NULL),
(11, 5, 'IDP-003-2026', 3, 0, 'ORI', 'USD', '2026-05-01', 90, '2026-07-30', 600.00, 'MIBANCO', 'pendiente', NULL);
