-- Tilapias App feature-pack SQL for phpMyAdmin
-- Scope: schema additions from 2026-04-26 through 2026-04-27
-- Assumptions:
-- 1. Base Laravel tables already exist: users, tanks, batches, task_projects
-- 2. Engine supports InnoDB + utf8mb4
-- 3. Run this once on the target database from phpMyAdmin SQL tab
-- 4. This script includes the final expected state of the schema for the features built in this project phase
-- 5. The snapshot-efficiency block is included here even though the intermediate 001300 migration file is not present in the repo

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE `batches`
  ADD COLUMN IF NOT EXISTS `biomass_current_kg` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `avg_weight_current_g`;

CREATE TABLE IF NOT EXISTS `batch_split_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `source_batch_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `fish_count_theoretical` INT UNSIGNED NOT NULL DEFAULT 0,
  `fish_count_counted` INT UNSIGNED NOT NULL DEFAULT 0,
  `fish_missing` INT UNSIGNED NOT NULL DEFAULT 0,
  `fish_moved_total` INT UNSIGNED NOT NULL DEFAULT 0,
  `fish_remaining_source` INT UNSIGNED NOT NULL DEFAULT 0,
  `biomass_moved_total_kg` INT UNSIGNED NOT NULL DEFAULT 0,
  `avg_weight_reference_g` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `notes` TEXT NULL,
  `revision_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `adjustment_reason` TEXT NULL,
  `last_adjusted_at` TIMESTAMP NULL DEFAULT NULL,
  `last_adjusted_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_batch_split_sessions_user_date` (`user_id`, `date`),
  KEY `idx_batch_split_sessions_source_date` (`source_batch_id`, `date`),
  KEY `idx_batch_split_sessions_last_adjusted_by` (`last_adjusted_by`),
  CONSTRAINT `fk_batch_split_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_split_sessions_source_batch` FOREIGN KEY (`source_batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_split_sessions_last_adjusted_by` FOREIGN KEY (`last_adjusted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `batch_split_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `batch_split_session_id` BIGINT UNSIGNED NULL,
  `source_batch_id` BIGINT UNSIGNED NOT NULL,
  `destination_batch_id` BIGINT UNSIGNED NOT NULL,
  `grading_label` VARCHAR(255) NULL,
  `date` DATE NOT NULL,
  `fish_count_theoretical` INT UNSIGNED NOT NULL DEFAULT 0,
  `fish_count_counted` INT UNSIGNED NOT NULL DEFAULT 0,
  `fish_moved` INT UNSIGNED NOT NULL DEFAULT 0,
  `fish_missing` INT UNSIGNED NOT NULL DEFAULT 0,
  `avg_weight_moved_g` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `biomass_moved_kg` INT UNSIGNED NOT NULL DEFAULT 0,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_batch_split_events_user_date` (`user_id`, `date`),
  KEY `idx_batch_split_events_source_date` (`source_batch_id`, `date`),
  KEY `idx_batch_split_events_dest_date` (`destination_batch_id`, `date`),
  KEY `idx_batch_split_events_session` (`batch_split_session_id`),
  CONSTRAINT `fk_batch_split_events_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_split_events_session` FOREIGN KEY (`batch_split_session_id`) REFERENCES `batch_split_sessions` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_batch_split_events_source_batch` FOREIGN KEY (`source_batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_split_events_destination_batch` FOREIGN KEY (`destination_batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `batch_split_plans` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `tank_id` BIGINT UNSIGNED NOT NULL,
  `task_project_id` BIGINT UNSIGNED NULL,
  `executed_batch_split_session_id` BIGINT UNSIGNED NULL,
  `planned_date` DATE NOT NULL,
  `reminder_date` DATE NULL,
  `followup_date` DATE NULL,
  `executed_at` TIMESTAMP NULL DEFAULT NULL,
  `reminder_days_before` INT UNSIGNED NOT NULL DEFAULT 0,
  `objective` VARCHAR(255) NOT NULL,
  `notes` TEXT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_batch_split_plans_user_batch_planned` (`user_id`, `batch_id`, `planned_date`),
  KEY `idx_batch_split_plans_tank` (`tank_id`),
  KEY `idx_batch_split_plans_task_project` (`task_project_id`),
  KEY `idx_batch_split_plans_executed_session` (`executed_batch_split_session_id`),
  CONSTRAINT `fk_batch_split_plans_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_split_plans_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_split_plans_tank` FOREIGN KEY (`tank_id`) REFERENCES `tanks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_split_plans_task_project` FOREIGN KEY (`task_project_id`) REFERENCES `task_projects` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_batch_split_plans_executed_session` FOREIGN KEY (`executed_batch_split_session_id`) REFERENCES `batch_split_sessions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_cost_entries` (
  `id` VARCHAR(120) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `category` VARCHAR(30) NOT NULL,
  `subcategory` VARCHAR(255) NOT NULL,
  `amount_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `quantity` DECIMAL(14,3) NULL,
  `unit` VARCHAR(20) NULL,
  `unit_cost_crc` DECIMAL(14,4) NULL,
  `source` VARCHAR(20) NOT NULL DEFAULT 'MANUAL',
  `ref_json` JSON NULL,
  `scope_json` JSON NULL,
  `notes` TEXT NULL,
  `created_at_client` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_finance_cost_entries_user_date` (`user_id`, `date`),
  KEY `idx_finance_cost_entries_user_category` (`user_id`, `category`),
  CONSTRAINT `fk_finance_cost_entries_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_sales_entries` (
  `id` VARCHAR(120) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `kg_sold` DECIMAL(14,3) NOT NULL DEFAULT 0,
  `price_crc_per_kg` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `total_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `customer_name` VARCHAR(255) NULL,
  `scope_json` JSON NULL,
  `notes` TEXT NULL,
  `created_at_client` TIMESTAMP NULL DEFAULT NULL,
  `updated_at_client` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_finance_sales_entries_user_date` (`user_id`, `date`),
  CONSTRAINT `fk_finance_sales_entries_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_harvest_entries` (
  `id` VARCHAR(120) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `kg_harvested` DECIMAL(14,3) NOT NULL DEFAULT 0,
  `kg_adjustment_loss` DECIMAL(12,3) NULL,
  `loss_reason` VARCHAR(255) NULL,
  `notes` TEXT NULL,
  `created_at_client` TIMESTAMP NULL DEFAULT NULL,
  `updated_at_client` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_finance_harvest_entries_user_date` (`user_id`, `date`),
  KEY `idx_finance_harvest_entries_user_batch` (`user_id`, `batch_id`),
  CONSTRAINT `fk_finance_harvest_entries_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_finance_harvest_entries_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_accounts` (
  `id` VARCHAR(120) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `code` VARCHAR(60) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `type` VARCHAR(20) NOT NULL,
  `parent_id` VARCHAR(120) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at_client` TIMESTAMP NULL DEFAULT NULL,
  `updated_at_client` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_finance_accounts_user_code` (`user_id`, `code`),
  CONSTRAINT `fk_finance_accounts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `finance_journal_entries` (
  `id` VARCHAR(120) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `memo` VARCHAR(1000) NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
  `lines_json` JSON NOT NULL,
  `ref_json` JSON NULL,
  `created_at_client` TIMESTAMP NULL DEFAULT NULL,
  `updated_at_client` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_finance_journal_entries_user_date` (`user_id`, `date`),
  KEY `idx_finance_journal_entries_user_status` (`user_id`, `status`),
  CONSTRAINT `fk_finance_journal_entries_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `batch_kpi_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `tank_id` BIGINT UNSIGNED NOT NULL,
  `batch_status` VARCHAR(20) NOT NULL DEFAULT 'active',
  `snapshot_date` DATE NOT NULL,
  `cycle_day` INT UNSIGNED NOT NULL DEFAULT 0,
  `stage_code` VARCHAR(20) NULL,
  `last_split_date` DATE NULL,
  `stage_days_elapsed` INT UNSIGNED NOT NULL DEFAULT 0,
  `uniformity_cv_pct` DECIMAL(10,2) NULL,
  `weight_spread_g` DECIMAL(10,2) NULL,
  `days_since_last_split` INT UNSIGNED NULL,
  `post_split_biomass_gain_kg` DECIMAL(12,2) NULL,
  `post_split_feed_kg` DECIMAL(12,3) NULL,
  `post_split_fcr_real` DECIMAL(12,3) NULL,
  `stage_biomass_gain_kg` DECIMAL(12,2) NULL,
  `stage_feed_kg` DECIMAL(12,3) NULL,
  `stage_fcr_real` DECIMAL(12,3) NULL,
  `stage_fcr_target` DECIMAL(12,3) NULL,
  `stage_fcr_gap` DECIMAL(12,3) NULL,
  `post_split_fcr_target` DECIMAL(12,3) NULL,
  `post_split_fcr_gap` DECIMAL(12,3) NULL,
  `fish_alive` INT UNSIGNED NOT NULL DEFAULT 0,
  `avg_weight_g` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `biomass_kg` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `total_cost_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `realized_revenue_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `harvested_kg_cumulative` DECIMAL(14,3) NOT NULL DEFAULT 0,
  `sold_kg_cumulative` DECIMAL(14,3) NOT NULL DEFAULT 0,
  `cash_recovery_pct` DECIMAL(12,2) NULL,
  `capital_locked_days` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `cycle_closed_at` DATE NULL,
  `harvest_completion_pct` DECIMAL(12,2) NULL,
  `sales_realization_pct` DECIMAL(12,2) NULL,
  `cycle_closure_ready` TINYINT(1) NOT NULL DEFAULT 0,
  `cycle_closure_state` VARCHAR(20) NOT NULL DEFAULT 'OPEN',
  `target_progress_pct` DECIMAL(12,2) NULL,
  `net_cashflow_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `roi_pct` DECIMAL(12,2) NULL,
  `projected_roi_pct` DECIMAL(12,2) NULL,
  `cost_per_kg_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `projected_revenue_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `projected_margin_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `margin_per_kg_crc` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `break_even_weight_g` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `days_to_target_estimated` INT UNSIGNED NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'HEALTHY',
  `growth_rate_g_per_day` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `mortality_rate_pct` DECIMAL(8,2) NOT NULL DEFAULT 0,
  `hidden_loss_fish` INT UNSIGNED NOT NULL DEFAULT 0,
  `hidden_loss_biomass_kg` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `recommended_feed_g` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `actual_feed_g` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `feed_variance_g` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `cumulative_feed_g` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `cumulative_feed_kg` DECIMAL(12,3) NOT NULL DEFAULT 0,
  `feed_conversion_ratio_real` DECIMAL(12,3) NULL,
  `density_fish_m3` DECIMAL(12,2) NULL,
  `density_biomass_kg_m3` DECIMAL(12,2) NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_batch_kpi_snapshots_batch_date` (`batch_id`, `snapshot_date`),
  KEY `idx_batch_kpi_snapshots_user_date` (`user_id`, `snapshot_date`),
  KEY `idx_batch_kpi_snapshots_tank_date` (`tank_id`, `snapshot_date`),
  CONSTRAINT `fk_batch_kpi_snapshots_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_kpi_snapshots_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_batch_kpi_snapshots_tank` FOREIGN KEY (`tank_id`) REFERENCES `tanks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `document_json` JSON NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_settings_user_id` (`user_id`),
  CONSTRAINT `fk_user_settings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_setting_versions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `version_no` INT UNSIGNED NOT NULL,
  `document_json` JSON NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_setting_versions_user_version` (`user_id`, `version_no`),
  CONSTRAINT `fk_user_setting_versions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;