--
-- MokoSuite Construction Tables
--

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_jobs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_number` VARCHAR(20) NOT NULL DEFAULT '',
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `address` TEXT,
  `city` VARCHAR(100) NOT NULL DEFAULT '',
  `state` VARCHAR(50) NOT NULL DEFAULT '',
  `postal_code` VARCHAR(20) NOT NULL DEFAULT '',
  `owner_contact_id` INT DEFAULT NULL,
  `architect_contact_id` INT DEFAULT NULL,
  `superintendent_id` INT DEFAULT NULL,
  `contract_value` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `approved_changes` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `revised_value` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `retention_pct` DECIMAL(5,2) NOT NULL DEFAULT 10.00,
  `phase` ENUM('preconstruction','active','punch_list','closeout','warranty','completed') NOT NULL DEFAULT 'preconstruction',
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `actual_start` DATE DEFAULT NULL,
  `actual_end` DATE DEFAULT NULL,
  `created` DATETIME NOT NULL,
  `modified` DATETIME DEFAULT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_job_number` (`job_number`),
  KEY `idx_owner` (`owner_contact_id`),
  KEY `idx_phase` (`phase`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_cost_codes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `division` VARCHAR(10) NOT NULL,
  `section` VARCHAR(20) NOT NULL DEFAULT '',
  `title` VARCHAR(255) NOT NULL,
  `budget_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `committed_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `actual_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `ordering` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_job` (`job_id`),
  KEY `idx_division` (`division`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_change_orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `co_number` INT UNSIGNED NOT NULL DEFAULT 1,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `days_impact` INT NOT NULL DEFAULT 0,
  `status` ENUM('draft','submitted','approved','rejected') NOT NULL DEFAULT 'draft',
  `submitted_at` DATETIME DEFAULT NULL,
  `approved_at` DATETIME DEFAULT NULL,
  `approved_by` INT DEFAULT NULL,
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_job` (`job_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_rfis` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `rfi_number` INT UNSIGNED NOT NULL DEFAULT 1,
  `subject` VARCHAR(255) NOT NULL,
  `question` TEXT NOT NULL,
  `spec_section` VARCHAR(50) NOT NULL DEFAULT '',
  `drawing_ref` VARCHAR(100) NOT NULL DEFAULT '',
  `ball_in_court` VARCHAR(100) NOT NULL DEFAULT '',
  `answer` TEXT,
  `status` ENUM('open','answered','closed') NOT NULL DEFAULT 'open',
  `submitted_by` INT DEFAULT NULL,
  `answered_by` INT DEFAULT NULL,
  `submitted_at` DATETIME NOT NULL,
  `answered_at` DATETIME DEFAULT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_job` (`job_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_submittals` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `submittal_number` INT UNSIGNED NOT NULL DEFAULT 1,
  `title` VARCHAR(255) NOT NULL,
  `spec_section` VARCHAR(50) NOT NULL DEFAULT '',
  `subcontractor_contact_id` INT DEFAULT NULL,
  `status` ENUM('pending','in_review','approved','approved_as_noted','rejected','resubmit') NOT NULL DEFAULT 'pending',
  `submitted_at` DATETIME DEFAULT NULL,
  `reviewed_at` DATETIME DEFAULT NULL,
  `reviewed_by` INT DEFAULT NULL,
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_job` (`job_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_daily_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `log_date` DATE NOT NULL,
  `weather` VARCHAR(100) NOT NULL DEFAULT '',
  `temperature_high` INT DEFAULT NULL,
  `temperature_low` INT DEFAULT NULL,
  `crew_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `work_performed` TEXT,
  `equipment_used` TEXT,
  `visitors` TEXT,
  `safety_incidents` TEXT,
  `delays` TEXT,
  `photos_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_by` INT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_job_date` (`job_id`, `log_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_pay_applications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `app_number` INT UNSIGNED NOT NULL DEFAULT 1,
  `period_from` DATE NOT NULL,
  `period_to` DATE NOT NULL,
  `contract_sum` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `change_orders_total` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `revised_contract` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `work_completed` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `materials_stored` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `total_earned` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `retention_held` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `previous_payments` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `current_due` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('draft','submitted','approved','paid') NOT NULL DEFAULT 'draft',
  `submitted_at` DATETIME DEFAULT NULL,
  `approved_at` DATETIME DEFAULT NULL,
  `paid_at` DATETIME DEFAULT NULL,
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_job` (`job_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_pay_app_lines` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `pay_app_id` INT UNSIGNED NOT NULL,
  `cost_code_id` INT UNSIGNED NOT NULL,
  `scheduled_value` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `previous_work` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `current_work` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `materials_stored` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `total_completed` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `pct_complete` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `balance_to_finish` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_pay_app` (`pay_app_id`),
  KEY `idx_cost_code` (`cost_code_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_lien_waivers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `vendor_contact_id` INT NOT NULL,
  `type` ENUM('conditional_partial','unconditional_partial','conditional_final','unconditional_final') NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `through_date` DATE DEFAULT NULL,
  `received` TINYINT NOT NULL DEFAULT 0,
  `received_at` DATETIME DEFAULT NULL,
  `document_path` VARCHAR(500) NOT NULL DEFAULT '',
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_job` (`job_id`),
  KEY `idx_vendor` (`vendor_contact_id`),
  KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_punch_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `location` VARCHAR(255) NOT NULL DEFAULT '',
  `description` TEXT NOT NULL,
  `responsible_contact_id` INT DEFAULT NULL,
  `priority` ENUM('high','medium','low') NOT NULL DEFAULT 'medium',
  `status` ENUM('open','in_progress','completed','verified') NOT NULL DEFAULT 'open',
  `photo_path` VARCHAR(500) NOT NULL DEFAULT '',
  `completed_at` DATETIME DEFAULT NULL,
  `verified_at` DATETIME DEFAULT NULL,
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_job` (`job_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_subcontracts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` INT UNSIGNED NOT NULL,
  `subcontractor_contact_id` INT NOT NULL,
  `trade` VARCHAR(100) NOT NULL DEFAULT '',
  `scope` TEXT,
  `contract_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `retention_pct` DECIMAL(5,2) NOT NULL DEFAULT 10.00,
  `status` ENUM('draft','executed','in_progress','completed','terminated') NOT NULL DEFAULT 'draft',
  `executed_at` DATETIME DEFAULT NULL,
  `insurance_gl_expires` DATE DEFAULT NULL,
  `insurance_wc_expires` DATE DEFAULT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_job` (`job_id`),
  KEY `idx_sub` (`subcontractor_contact_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;