-- CreateTable
CREATE TABLE `exporters` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    UNIQUE INDEX `exporters_code_key`(`code`),
    INDEX `exporters_active_idx`(`active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `importers` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    UNIQUE INDEX `importers_code_key`(`code`),
    INDEX `importers_active_idx`(`active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `ports` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `is_local` BOOLEAN NOT NULL DEFAULT false,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    UNIQUE INDEX `ports_code_key`(`code`),
    INDEX `ports_active_idx`(`active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `currencies` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    UNIQUE INDEX `currencies_code_key`(`code`),
    INDEX `currencies_active_idx`(`active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `flags` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    UNIQUE INDEX `flags_code_key`(`code`),
    INDEX `flags_active_idx`(`active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `bank_accounts` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `acc_num` VARCHAR(191) NOT NULL,
    `bank_name` VARCHAR(191) NOT NULL,
    `branch_name` VARCHAR(191) NULL,
    `ifsc_code` VARCHAR(191) NULL,
    `acc_type` ENUM('SAVING', 'CURRENT') NOT NULL,
    `notes` VARCHAR(191) NULL,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    UNIQUE INDEX `bank_accounts_code_key`(`code`),
    INDEX `bank_accounts_active_idx`(`active`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `shipments` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `ship_refnum` VARCHAR(191) NOT NULL,
    `exporter_id` BIGINT NOT NULL,
    `importer_id` BIGINT NOT NULL,
    `port_id` BIGINT NOT NULL,
    `currency_id` BIGINT NOT NULL,
    `be_num` VARCHAR(191) NULL,
    `be_date` DATETIME(3) NULL,
    `be_status` ENUM('PAYMENT_PENDING', 'PARTIALLY_PAID', 'FULLY_PAID') NULL,
    `be_submitted` BOOLEAN NOT NULL DEFAULT false,
    `status` ENUM('DRAFT', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED') NULL,
    `notes` VARCHAR(191) NULL,
    `be_file_name` VARCHAR(191) NULL,
    `be_file_type` VARCHAR(191) NULL,
    `be_file_path` VARCHAR(191) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    INDEX `shipments_exporter_id_idx`(`exporter_id`),
    INDEX `shipments_importer_id_idx`(`importer_id`),
    INDEX `shipments_port_id_idx`(`port_id`),
    INDEX `shipments_currency_id_idx`(`currency_id`),
    INDEX `shipments_status_idx`(`status`),
    INDEX `shipments_be_status_idx`(`be_status`),
    INDEX `shipments_ship_refnum_idx`(`ship_refnum`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `shipment_invoices` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `shipment_id` BIGINT NOT NULL,
    `inv_refnum` VARCHAR(191) NOT NULL,
    `inv_date` DATETIME(3) NOT NULL,
    `inv_currency` BIGINT NOT NULL,
    `inv_amount` DECIMAL(15, 2) NOT NULL,
    `inv_status` ENUM('PENDING', 'PARTIAL', 'PAID', 'CANCELLED') NOT NULL,
    `notes` VARCHAR(191) NULL,
    `file_name` VARCHAR(191) NULL,
    `file_type` VARCHAR(191) NULL,
    `file_path` VARCHAR(191) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    INDEX `shipment_invoices_shipment_id_idx`(`shipment_id`),
    INDEX `shipment_invoices_inv_status_idx`(`inv_status`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `shipment_payments` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `shipment_id` BIGINT NOT NULL,
    `pay_date` DATETIME(3) NOT NULL,
    `pay_refnum` VARCHAR(191) NOT NULL,
    `pay_invnum` VARCHAR(191) NULL,
    `pay_currency` BIGINT NOT NULL,
    `pay_amount` DECIMAL(15, 2) NOT NULL,
    `pay_bankacc_id` BIGINT NOT NULL,
    `notes` VARCHAR(191) NULL,
    `file_name` VARCHAR(191) NULL,
    `file_type` VARCHAR(191) NULL,
    `file_path` VARCHAR(191) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    INDEX `shipment_payments_shipment_id_idx`(`shipment_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `shipment_credit_notes` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `shipment_id` BIGINT NOT NULL,
    `cn_refnum` VARCHAR(191) NOT NULL,
    `cn_date` DATETIME(3) NOT NULL,
    `cn_currency` BIGINT NOT NULL,
    `cn_amount` DECIMAL(15, 2) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `file_name` VARCHAR(191) NULL,
    `file_type` VARCHAR(191) NULL,
    `file_path` VARCHAR(191) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    INDEX `shipment_credit_notes_shipment_id_idx`(`shipment_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `shipment_debit_notes` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `shipment_id` BIGINT NOT NULL,
    `dn_refnum` VARCHAR(191) NOT NULL,
    `dn_date` DATETIME(3) NOT NULL,
    `dn_currency` BIGINT NOT NULL,
    `dn_amount` DECIMAL(15, 2) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `file_name` VARCHAR(191) NULL,
    `file_type` VARCHAR(191) NULL,
    `file_path` VARCHAR(191) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    INDEX `shipment_debit_notes_shipment_id_idx`(`shipment_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `shipment_refunds` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `shipment_id` BIGINT NOT NULL,
    `rf_invnum` VARCHAR(191) NULL,
    `rf_date` DATETIME(3) NOT NULL,
    `rf_currency` BIGINT NOT NULL,
    `rf_amount` DECIMAL(15, 2) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `file_name` VARCHAR(191) NULL,
    `file_type` VARCHAR(191) NULL,
    `file_path` VARCHAR(191) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    INDEX `shipment_refunds_shipment_id_idx`(`shipment_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `shipment_adjustments` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `shipment_id` BIGINT NOT NULL,
    `adj_refnum` VARCHAR(191) NOT NULL,
    `adj_type` ENUM('CREDIT', 'DEBIT') NOT NULL,
    `adj_currency` BIGINT NOT NULL,
    `adj_amount` DECIMAL(15, 2) NOT NULL,
    `notes` VARCHAR(191) NULL,
    `file_name` VARCHAR(191) NULL,
    `file_type` VARCHAR(191) NULL,
    `file_path` VARCHAR(191) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    INDEX `shipment_adjustments_shipment_id_idx`(`shipment_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `shipment_flags` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `shipment_id` BIGINT NOT NULL,
    `flag_id` BIGINT NOT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `created_by` BIGINT NOT NULL,
    `updated_by` BIGINT NOT NULL,

    INDEX `shipment_flags_shipment_id_idx`(`shipment_id`),
    INDEX `shipment_flags_flag_id_idx`(`flag_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `users` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `password` VARCHAR(191) NOT NULL,
    `role` ENUM('SUPER_ADMIN', 'ADMIN', 'USER') NOT NULL DEFAULT 'USER',
    `active` BOOLEAN NOT NULL DEFAULT true,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `users_email_key`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `user_preferences` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT NOT NULL,
    `theme` ENUM('LIGHT', 'DARK', 'SYSTEM') NOT NULL DEFAULT 'SYSTEM',
    `menu_layout` ENUM('SIDEBAR', 'TOPNAV') NOT NULL DEFAULT 'SIDEBAR',
    `density` ENUM('COMFORTABLE', 'COMPACT', 'DENSE') NOT NULL DEFAULT 'COMPACT',
    `font_family` VARCHAR(191) NOT NULL DEFAULT 'Inter',
    `font_size` ENUM('SM', 'MD', 'LG') NOT NULL DEFAULT 'MD',
    `sidebar_collapsed` BOOLEAN NOT NULL DEFAULT false,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `user_preferences_user_id_key`(`user_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `shipments` ADD CONSTRAINT `shipments_exporter_id_fkey` FOREIGN KEY (`exporter_id`) REFERENCES `exporters`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipments` ADD CONSTRAINT `shipments_importer_id_fkey` FOREIGN KEY (`importer_id`) REFERENCES `importers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipments` ADD CONSTRAINT `shipments_port_id_fkey` FOREIGN KEY (`port_id`) REFERENCES `ports`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipments` ADD CONSTRAINT `shipments_currency_id_fkey` FOREIGN KEY (`currency_id`) REFERENCES `currencies`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_invoices` ADD CONSTRAINT `shipment_invoices_shipment_id_fkey` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_invoices` ADD CONSTRAINT `shipment_invoices_inv_currency_fkey` FOREIGN KEY (`inv_currency`) REFERENCES `currencies`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_payments` ADD CONSTRAINT `shipment_payments_shipment_id_fkey` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_payments` ADD CONSTRAINT `shipment_payments_pay_currency_fkey` FOREIGN KEY (`pay_currency`) REFERENCES `currencies`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_payments` ADD CONSTRAINT `shipment_payments_pay_bankacc_id_fkey` FOREIGN KEY (`pay_bankacc_id`) REFERENCES `bank_accounts`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_credit_notes` ADD CONSTRAINT `shipment_credit_notes_shipment_id_fkey` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_credit_notes` ADD CONSTRAINT `shipment_credit_notes_cn_currency_fkey` FOREIGN KEY (`cn_currency`) REFERENCES `currencies`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_debit_notes` ADD CONSTRAINT `shipment_debit_notes_shipment_id_fkey` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_debit_notes` ADD CONSTRAINT `shipment_debit_notes_dn_currency_fkey` FOREIGN KEY (`dn_currency`) REFERENCES `currencies`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_refunds` ADD CONSTRAINT `shipment_refunds_shipment_id_fkey` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_refunds` ADD CONSTRAINT `shipment_refunds_rf_currency_fkey` FOREIGN KEY (`rf_currency`) REFERENCES `currencies`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_adjustments` ADD CONSTRAINT `shipment_adjustments_shipment_id_fkey` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_adjustments` ADD CONSTRAINT `shipment_adjustments_adj_currency_fkey` FOREIGN KEY (`adj_currency`) REFERENCES `currencies`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_flags` ADD CONSTRAINT `shipment_flags_shipment_id_fkey` FOREIGN KEY (`shipment_id`) REFERENCES `shipments`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `shipment_flags` ADD CONSTRAINT `shipment_flags_flag_id_fkey` FOREIGN KEY (`flag_id`) REFERENCES `flags`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `user_preferences` ADD CONSTRAINT `user_preferences_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;