-- Version20260611120000
-- Create report_template and report_configuration tables with seed data
-- for PACKAGES, WAREHOUSE_RECEIPT and GUIA templates

-- =========================
-- UP
-- =========================

CREATE TABLE `report_template` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` LONGTEXT DEFAULT NULL,
    `category` VARCHAR(100) DEFAULT NULL,
    `default_config` JSON DEFAULT NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    UNIQUE INDEX `UNIQ_report_template_code` (`code`),
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE `report_configuration` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `maincompany_id` INT NOT NULL,
    `report_template_id` INT NOT NULL,
    `created_by` INT DEFAULT NULL,
    `name` VARCHAR(255) NOT NULL,
    `is_default` TINYINT(1) NOT NULL DEFAULT 0,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `config` JSON DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    INDEX `IDX_rc_maincompany` (`maincompany_id`),
    INDEX `IDX_rc_template` (`report_template_id`),
    INDEX `IDX_rc_created_by` (`created_by`),
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE `report_configuration`
    ADD CONSTRAINT `FK_rc_maincompany`
    FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`);

ALTER TABLE `report_configuration`
    ADD CONSTRAINT `FK_rc_template`
    FOREIGN KEY (`report_template_id`) REFERENCES `report_template` (`id`);

ALTER TABLE `report_configuration`
    ADD CONSTRAINT `FK_rc_created_by`
    FOREIGN KEY (`created_by`) REFERENCES `user` (`id`) ON DELETE SET NULL;

INSERT INTO `report_template` (`code`, `name`, `description`, `category`, `default_config`, `active`, `created_at`, `updated_at`)
VALUES (
    'PACKAGES',
    'Packages Report',
    NULL,
    NULL,
    '{"branding":{"showLogo":true,"showCompanyName":true,"headerTitle":"Packages Report"},"sections":[{"key":"detail_table","visible":true,"order":1,"title":"Package Detail","columns":[{"key":"number","visible":true,"order":1,"label":"Number"},{"key":"tracking","visible":true,"order":2,"label":"Tracking"},{"key":"weight","visible":true,"order":3,"label":"Weight"},{"key":"shipper","visible":true,"order":4,"label":"Shipper"},{"key":"receiver","visible":true,"order":5,"label":"Receiver"},{"key":"arriveDate","visible":true,"order":6,"label":"Arrive Date"}]}]}',
    1,
    NOW(),
    NOW()
);

INSERT INTO `report_template` (`code`, `name`, `description`, `category`, `default_config`, `active`, `created_at`, `updated_at`)
VALUES (
    'WAREHOUSE_RECEIPT',
    'Warehouse Receipt Report',
    NULL,
    NULL,
    '{"branding":{"showLogo":true,"showCompanyName":true,"headerTitle":"Warehouse Receipt Report"},"sections":[{"key":"detail_table","visible":true,"order":1,"title":"Warehouse Receipt Detail","columns":[{"key":"number","visible":true,"order":1,"label":"Number"},{"key":"shipper","visible":true,"order":2,"label":"Shipper"},{"key":"receiver","visible":true,"order":3,"label":"Receiver"},{"key":"status","visible":true,"order":4,"label":"Status"},{"key":"totalWeight","visible":true,"order":5,"label":"Total Weight"}]}]}',
    1,
    NOW(),
    NOW()
);

INSERT INTO `report_template` (`code`, `name`, `description`, `category`, `default_config`, `active`, `created_at`, `updated_at`)
VALUES (
    'GUIA',
    'Guía Report',
    NULL,
    NULL,
    '{"branding":{"showLogo":true,"showCompanyName":true,"headerTitle":"Guía Report"},"sections":[{"key":"detail_table","visible":true,"order":1,"title":"Guía Detail","columns":[{"key":"number","visible":true,"order":1,"label":"Number"},{"key":"shipper","visible":true,"order":2,"label":"Shipper"},{"key":"receiver","visible":true,"order":3,"label":"Receiver"},{"key":"status","visible":true,"order":4,"label":"Status"},{"key":"creationDate","visible":true,"order":5,"label":"Creation Date"},{"key":"totalPaid","visible":true,"order":6,"label":"Total Paid"}]}]}',
    1,
    NOW(),
    NOW()
);

-- =========================
-- DOWN
-- =========================

ALTER TABLE `report_configuration` DROP FOREIGN KEY `FK_rc_created_by`;
ALTER TABLE `report_configuration` DROP FOREIGN KEY `FK_rc_template`;
ALTER TABLE `report_configuration` DROP FOREIGN KEY `FK_rc_maincompany`;

DROP TABLE `report_configuration`;
DROP TABLE `report_template`;