/* ===================================================================
   CivicMatrix – Projects Module (tenant-safe FKs)
   Core 5 Tables + project_types, prefix-scoped constraint names
   =================================================================== */


/* 1) CORE TABLE */
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_projects` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `project_uid` VARCHAR(128) NOT NULL,
  `project_name` TEXT NOT NULL,

  `project_type` VARCHAR(128) NOT NULL,
  `role_in_project` VARCHAR(128) NOT NULL,
  `department` VARCHAR(128) NOT NULL,
  `project_year` YEAR NOT NULL,
  `project_sector` VARCHAR(128) NOT NULL,
  `project_sub_sector` VARCHAR(128) NOT NULL,
  `project_status` VARCHAR(64) NOT NULL,
  `project_category` VARCHAR(128) NOT NULL,

  `project_scheme` TEXT NULL,
  `project_sanctioned_on` DATE NULL,
  `project_commission_date` DATE NULL,

  `project_cost_value` DECIMAL(14,2) NULL,
  `project_cost_unit` ENUM('L','Cr','Number') NULL,

  `project_period_value` INT NULL,
  `project_period_unit` ENUM('Days','Months','Years') NULL,

  `project_overview` LONGTEXT NULL,

  `this_constituency` TINYINT(1) NOT NULL DEFAULT '0',
  `project_area_type` VARCHAR(64) NOT NULL,

  `publish_ground_team` TINYINT(1) NOT NULL DEFAULT '0',
  `publish_website` TINYINT(1) NOT NULL DEFAULT '0',
  `publish_public_report` TINYINT(1) NOT NULL DEFAULT '0',
  `publish_whatsapp` TINYINT(1) NOT NULL DEFAULT '0',
  `publish_email` TINYINT(1) NOT NULL DEFAULT '0',
  `publish_civic_people` TINYINT(1) NOT NULL DEFAULT '0',
  `bookmarked` TINYINT(1) NOT NULL DEFAULT '0',

  `cover_file_id` INT NULL,
  `cover_thumb_s3_key` TEXT NULL,

  `created_by` INT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `soft_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `deleted_on` DATETIME NULL,
  `deleted_by` INT NULL,
  `updated_by` INT NULL,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `{{TABLE_PREFIX}}_ux_projects_uid` (`project_uid`),
  KEY `{{TABLE_PREFIX}}_idx_projects_status_year` (`project_status`, `project_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


/* 2) REGIONS TABLE */
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_project_regions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `project_id` INT NOT NULL,
  `region_type` ENUM('parliament','assembly','constituency','localbody_type','localbody','ward','booth') NOT NULL,
  `region_code` VARCHAR(128) NULL,
  `region_label` TEXT NOT NULL,
  `soft_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `deleted_on` DATETIME NULL,
  `deleted_by` INT NULL,
  PRIMARY KEY (`id`),
  KEY `{{TABLE_PREFIX}}_idx_proj_regions_project_type` (`project_id`, `region_type`),
  CONSTRAINT `{{TABLE_PREFIX}}_fk_proj_regions_project`
    FOREIGN KEY (`project_id`)
    REFERENCES `{{TABLE_PREFIX}}_projects`(`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


/* 3) CONTRIBUTIONS TABLE */
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_project_contributions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `project_id` INT NOT NULL,
  `contributor_type` ENUM('central','state','local_body','csr','crowd','others') NOT NULL,
  `amount_value` DECIMAL(14,2) NOT NULL,
  `amount_unit` ENUM('L','Cr') NOT NULL,
  `remarks` TEXT NULL,
  `soft_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `deleted_on` DATETIME NULL,
  `deleted_by` INT NULL,
  PRIMARY KEY (`id`),
  KEY `{{TABLE_PREFIX}}_idx_proj_contrib_project_type` (`project_id`, `contributor_type`),
  CONSTRAINT `{{TABLE_PREFIX}}_fk_proj_contrib_project`
    FOREIGN KEY (`project_id`)
    REFERENCES `{{TABLE_PREFIX}}_projects`(`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


/* 4) VENDORS TABLE */
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_project_vendors` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `project_id` INT NOT NULL,
  `vendor_type` VARCHAR(128) NULL,
  `vendor_name` TEXT NULL,
  `vendor_number` VARCHAR(64) NULL,
  `soft_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `deleted_on` DATETIME NULL,
  `deleted_by` INT NULL,
  PRIMARY KEY (`id`),
  KEY `{{TABLE_PREFIX}}_idx_proj_vendors_project` (`project_id`),
  CONSTRAINT `{{TABLE_PREFIX}}_fk_proj_vendors_project`
    FOREIGN KEY (`project_id`)
    REFERENCES `{{TABLE_PREFIX}}_projects`(`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


/* 5) FILE LINKAGE TABLE */
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_project_files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `project_id` INT NOT NULL,
  `file_id` INT NOT NULL,
  `linked_type` ENUM('gallery','attachment') NOT NULL,
  `uploaded_by` INT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `{{TABLE_PREFIX}}_idx_proj_files_project_type` (`project_id`, `linked_type`),
  KEY `{{TABLE_PREFIX}}_idx_proj_files_file` (`file_id`),
  CONSTRAINT `{{TABLE_PREFIX}}_fk_proj_files_project`
    FOREIGN KEY (`project_id`)
    REFERENCES `{{TABLE_PREFIX}}_projects`(`id`)
    ON DELETE CASCADE
  -- Optional FK to file_manager (enable when verified safe)
  -- , CONSTRAINT `{{TABLE_PREFIX}}_fk_proj_files_file`
  --     FOREIGN KEY (`file_id`)
  --     REFERENCES `{{TABLE_PREFIX}}_file_manager`(`id`)
  --     ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


/* 6) PROJECT TYPES TABLE */
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_project_types` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `type_name` VARCHAR(128) NOT NULL,
  `description` TEXT,
  `order_number` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `{{TABLE_PREFIX}}_ux_project_types_name` (`type_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


/* 7) ADD PROJECT_TYPE_ID FK (tenant-safe key name) */
ALTER TABLE `{{TABLE_PREFIX}}_projects`
  ADD COLUMN `project_type_id` INT NOT NULL AFTER `project_name`,
  ADD CONSTRAINT `{{TABLE_PREFIX}}_fk_projects_type`
    FOREIGN KEY (`project_type_id`)
    REFERENCES `{{TABLE_PREFIX}}_project_types`(`id`)
    ON UPDATE CASCADE
    ON DELETE RESTRICT;


/* 8) DROP OLD COLUMN (obsolete after FK addition) */
ALTER TABLE `{{TABLE_PREFIX}}_projects`
  DROP COLUMN `project_type`;
