-- CreateTable
CREATE TABLE `core_tenants` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 1,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `core_users` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'user') NOT NULL,
    `status` TINYINT NOT NULL DEFAULT 1,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

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

-- CreateTable
CREATE TABLE `core_customers` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `address` VARCHAR(500) NULL,
    `phone` VARCHAR(50) NULL,
    `email` VARCHAR(255) NULL,
    `updated_by` ENUM('user', 'survey') NOT NULL DEFAULT 'user',
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `idx_customer_ident`(`tenant_id`, `name`, `address`(100), `phone`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `core_customer_assigns` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `customer_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `role` ENUM('main', 'sub') NOT NULL DEFAULT 'main',
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `idx_assign_customer`(`customer_id`),
    INDEX `idx_assign_user`(`user_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `master_custom_field_definitions` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `target_type` ENUM('customer', 'daily_report') NOT NULL,
    `field_key` VARCHAR(100) NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `field_type` ENUM('text', 'number', 'date', 'select', 'multi_select', 'checkbox', 'radio') NOT NULL,
    `options` JSON NULL,
    `required` TINYINT NOT NULL DEFAULT 0,
    `searchable` TINYINT NOT NULL DEFAULT 0,
    `list_visible` TINYINT NOT NULL DEFAULT 0,
    `sort_order` INTEGER NOT NULL DEFAULT 0,
    `active` TINYINT NOT NULL DEFAULT 1,
    `version` INTEGER NOT NULL DEFAULT 1,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `idx_custom_field`(`tenant_id`, `target_type`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `core_customer_custom_values` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `customer_id` BIGINT NOT NULL,
    `field_definition_id` BIGINT NOT NULL,
    `value` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `idx_ccv_customer`(`customer_id`),
    INDEX `idx_ccv_field`(`field_definition_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `core_daily_reports` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `customer_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `content` TEXT NULL,
    `next_action` TEXT NULL,
    `next_action_date` DATE NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `idx_dr_customer`(`customer_id`),
    INDEX `idx_dr_next_action`(`next_action_date`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `core_daily_report_custom_values` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `daily_report_id` BIGINT NOT NULL,
    `field_definition_id` BIGINT NOT NULL,
    `value` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `log_customer_histories` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `customer_id` BIGINT NOT NULL,
    `changed_by` ENUM('user', 'survey') NOT NULL,
    `before_json` JSON NULL,
    `after_json` JSON NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `idx_history_customer`(`customer_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `core_surveys` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `token` VARCHAR(255) NOT NULL,
    `expires_at` DATETIME(3) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE `core_document_templates` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NULL,
    `file_path` VARCHAR(500) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `core_users` ADD CONSTRAINT `core_users_tenant_id_fkey` FOREIGN KEY (`tenant_id`) REFERENCES `core_tenants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_customers` ADD CONSTRAINT `core_customers_tenant_id_fkey` FOREIGN KEY (`tenant_id`) REFERENCES `core_tenants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_customer_assigns` ADD CONSTRAINT `core_customer_assigns_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `core_customers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_customer_assigns` ADD CONSTRAINT `core_customer_assigns_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `core_users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `master_custom_field_definitions` ADD CONSTRAINT `master_custom_field_definitions_tenant_id_fkey` FOREIGN KEY (`tenant_id`) REFERENCES `core_tenants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_customer_custom_values` ADD CONSTRAINT `core_customer_custom_values_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `core_customers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_customer_custom_values` ADD CONSTRAINT `core_customer_custom_values_field_definition_id_fkey` FOREIGN KEY (`field_definition_id`) REFERENCES `master_custom_field_definitions`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_daily_reports` ADD CONSTRAINT `core_daily_reports_tenant_id_fkey` FOREIGN KEY (`tenant_id`) REFERENCES `core_tenants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_daily_reports` ADD CONSTRAINT `core_daily_reports_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `core_customers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_daily_reports` ADD CONSTRAINT `core_daily_reports_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `core_users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_daily_report_custom_values` ADD CONSTRAINT `core_daily_report_custom_values_daily_report_id_fkey` FOREIGN KEY (`daily_report_id`) REFERENCES `core_daily_reports`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_daily_report_custom_values` ADD CONSTRAINT `core_daily_report_custom_values_field_definition_id_fkey` FOREIGN KEY (`field_definition_id`) REFERENCES `master_custom_field_definitions`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `log_customer_histories` ADD CONSTRAINT `log_customer_histories_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `core_customers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_surveys` ADD CONSTRAINT `core_surveys_tenant_id_fkey` FOREIGN KEY (`tenant_id`) REFERENCES `core_tenants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `core_document_templates` ADD CONSTRAINT `core_document_templates_tenant_id_fkey` FOREIGN KEY (`tenant_id`) REFERENCES `core_tenants`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
