-- AlterTable
ALTER TABLE `core_customers` ADD COLUMN `status` VARCHAR(20) NOT NULL DEFAULT '対応中';

-- CreateTable
CREATE TABLE `core_tasks` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `customer_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `due_date` DATE NOT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
    `completed_at` DATETIME(3) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `idx_tasks_tenant`(`tenant_id`),
    INDEX `idx_tasks_customer`(`customer_id`),
    INDEX `idx_tasks_user`(`user_id`),
    INDEX `idx_tasks_due_date`(`due_date`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `core_properties` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `address` VARCHAR(500) NULL,
    `property_type` VARCHAR(50) NULL,
    `price` BIGINT NULL,
    `land_area` DECIMAL(10, 2) NULL,
    `building_area` DECIMAL(10, 2) NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'active',
    `notes` TEXT NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

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

-- CreateTable
CREATE TABLE `core_customer_properties` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `customer_id` BIGINT NOT NULL,
    `property_id` BIGINT NOT NULL,
    `relation_type` VARCHAR(50) NOT NULL DEFAULT 'interested',
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

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

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

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

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

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

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

-- AddForeignKey
ALTER TABLE `core_customer_properties` ADD CONSTRAINT `core_customer_properties_property_id_fkey` FOREIGN KEY (`property_id`) REFERENCES `core_properties`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
