-- Catalogo de detracciones y cuotas de facturacion

CREATE TABLE IF NOT EXISTS catalogo_detracciones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(10) NOT NULL,
    descripcion VARCHAR(180) NOT NULL,
    porcentaje DECIMAL(6,2) NOT NULL DEFAULT 0,
    limite_minimo DECIMAL(14,2) NOT NULL DEFAULT 0,
    comentario VARCHAR(255) NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_catalogo_detracciones_codigo (codigo),
    KEY idx_catalogo_detracciones_descripcion (descripcion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO catalogo_detracciones (codigo, descripcion, porcentaje, limite_minimo, comentario, estado, created_at) VALUES
('01', 'Intermediacion laboral y tercerizacion', 12.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('02', 'Arrendamiento de bienes', 10.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('03', 'Mantenimiento y reparacion de bienes muebles', 12.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('04', 'Movimiento de carga', 10.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('05', 'Otros servicios empresariales', 12.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('06', 'Comision mercantil', 10.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('07', 'Fabricacion de bienes por encargo', 10.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('08', 'Servicio de transporte de personas', 10.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('09', 'Contratos de construccion', 4.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('10', 'Demas servicios gravados con IGV', 12.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW()),
('11', 'Servicio de beneficio de minerales metalicos gravado con IGV', 12.00, 700.00, 'Servicio sujeto a detraccion segun anexo 3 SUNAT', 1, NOW());

SET @sql_add_detraccion_item := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE catalogo_items ADD COLUMN catalogo_detraccion_id BIGINT UNSIGNED NULL AFTER afectacion_igv_id',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND column_name = 'catalogo_detraccion_id'
);
PREPARE stmt_add_detraccion_item FROM @sql_add_detraccion_item;
EXECUTE stmt_add_detraccion_item;
DEALLOCATE PREPARE stmt_add_detraccion_item;

SET @sql_add_detraccion_item_idx := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE catalogo_items ADD KEY idx_catalogo_detraccion (catalogo_detraccion_id)',
        'SELECT 1'
    )
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND index_name = 'idx_catalogo_detraccion'
);
PREPARE stmt_add_detraccion_item_idx FROM @sql_add_detraccion_item_idx;
EXECUTE stmt_add_detraccion_item_idx;
DEALLOCATE PREPARE stmt_add_detraccion_item_idx;

SET @sql_add_detraccion_item_fk := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE catalogo_items ADD CONSTRAINT fk_catalogo_item_detraccion FOREIGN KEY (catalogo_detraccion_id) REFERENCES catalogo_detracciones(id)',
        'SELECT 1'
    )
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND constraint_name = 'fk_catalogo_item_detraccion'
);
PREPARE stmt_add_detraccion_item_fk FROM @sql_add_detraccion_item_fk;
EXECUTE stmt_add_detraccion_item_fk;
DEALLOCATE PREPARE stmt_add_detraccion_item_fk;

SET @sql_add_comprobante_detraccion_id := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN detraccion_id BIGINT UNSIGNED NULL AFTER forma_pago_id',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'detraccion_id'
);
PREPARE stmt_add_comprobante_detraccion_id FROM @sql_add_comprobante_detraccion_id;
EXECUTE stmt_add_comprobante_detraccion_id;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_id;

SET @sql_add_comprobante_detraccion_codigo := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN detraccion_codigo VARCHAR(10) NULL AFTER detraccion_id',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'detraccion_codigo'
);
PREPARE stmt_add_comprobante_detraccion_codigo FROM @sql_add_comprobante_detraccion_codigo;
EXECUTE stmt_add_comprobante_detraccion_codigo;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_codigo;

SET @sql_add_comprobante_detraccion_desc := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN detraccion_descripcion VARCHAR(180) NULL AFTER detraccion_codigo',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'detraccion_descripcion'
);
PREPARE stmt_add_comprobante_detraccion_desc FROM @sql_add_comprobante_detraccion_desc;
EXECUTE stmt_add_comprobante_detraccion_desc;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_desc;

SET @sql_add_comprobante_detraccion_pct := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN detraccion_porcentaje DECIMAL(6,2) NOT NULL DEFAULT 0 AFTER detraccion_descripcion',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'detraccion_porcentaje'
);
PREPARE stmt_add_comprobante_detraccion_pct FROM @sql_add_comprobante_detraccion_pct;
EXECUTE stmt_add_comprobante_detraccion_pct;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_pct;

SET @sql_add_comprobante_detraccion_base := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN detraccion_base DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER detraccion_porcentaje',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'detraccion_base'
);
PREPARE stmt_add_comprobante_detraccion_base FROM @sql_add_comprobante_detraccion_base;
EXECUTE stmt_add_comprobante_detraccion_base;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_base;

SET @sql_add_comprobante_detraccion_monto := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN detraccion_monto DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER detraccion_base',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'detraccion_monto'
);
PREPARE stmt_add_comprobante_detraccion_monto FROM @sql_add_comprobante_detraccion_monto;
EXECUTE stmt_add_comprobante_detraccion_monto;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_monto;

SET @sql_add_comprobante_detraccion_limite := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN detraccion_limite_minimo DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER detraccion_monto',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'detraccion_limite_minimo'
);
PREPARE stmt_add_comprobante_detraccion_limite FROM @sql_add_comprobante_detraccion_limite;
EXECUTE stmt_add_comprobante_detraccion_limite;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_limite;

SET @sql_add_comprobante_detraccion_aplica := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD COLUMN detraccion_aplica TINYINT(1) NOT NULL DEFAULT 0 AFTER detraccion_limite_minimo',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND column_name = 'detraccion_aplica'
);
PREPARE stmt_add_comprobante_detraccion_aplica FROM @sql_add_comprobante_detraccion_aplica;
EXECUTE stmt_add_comprobante_detraccion_aplica;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_aplica;

SET @sql_add_comprobante_detraccion_fk := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE facturacion_comprobantes ADD CONSTRAINT fk_fact_comprobante_detraccion FOREIGN KEY (detraccion_id) REFERENCES catalogo_detracciones(id)',
        'SELECT 1'
    )
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobantes'
      AND constraint_name = 'fk_fact_comprobante_detraccion'
);
PREPARE stmt_add_comprobante_detraccion_fk FROM @sql_add_comprobante_detraccion_fk;
EXECUTE stmt_add_comprobante_detraccion_fk;
DEALLOCATE PREPARE stmt_add_comprobante_detraccion_fk;

CREATE TABLE IF NOT EXISTS facturacion_comprobante_cuotas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comprobante_id BIGINT UNSIGNED NOT NULL,
    numero_cuota INT UNSIGNED NOT NULL,
    fecha_vencimiento DATE NOT NULL,
    monto DECIMAL(14,2) NOT NULL DEFAULT 0,
    estado ENUM('PENDIENTE','PAGADA','ANULADA') NOT NULL DEFAULT 'PENDIENTE',
    observacion VARCHAR(255) NULL,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_fact_cuota_comprobante (comprobante_id, numero_cuota),
    KEY idx_fact_cuota_comprobante (comprobante_id),
    CONSTRAINT fk_fact_cuota_comprobante FOREIGN KEY (comprobante_id) REFERENCES facturacion_comprobantes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;