soil-report-ai/database/migrations/003_paddock_schema_fixes.sql

-- =============================================================================
-- Migration 003 — Paddock schema fixes
-- Run once: mysql -u <user> -p cropmonitor < 003_paddock_schema_fixes.sql
-- =============================================================================

-- ── block_info fixes ──────────────────────────────────────────────────────────

-- gps was INT — GPS strings like "-33.8688, 151.2093" were silently lost
ALTER TABLE `block_info`
    MODIFY COLUMN `gps` VARCHAR(100) DEFAULT NULL;

-- area was INT — fractional hectares (e.g. 12.5) were truncated
ALTER TABLE `block_info`
    MODIFY COLUMN `area` DECIMAL(10,2) DEFAULT NULL;

-- soil type is captured in the create/edit modal but had no column
ALTER TABLE `block_info`
    ADD COLUMN `analysis_type` VARCHAR(30) DEFAULT NULL
    AFTER `area`;

-- utf8 for all text fields (was latin1)
ALTER TABLE `block_info`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- ── plant_records — add block_id for paddock linking ─────────────────────────

ALTER TABLE `plant_records`
    ADD COLUMN `block_id` VARCHAR(50) DEFAULT NULL
    AFTER `site_id`;

CREATE INDEX `idx_plant_block` ON `plant_records` (`modx_user_id`, `block_id`);


-- ── water_records — add block_id for paddock linking ─────────────────────────

ALTER TABLE `water_records`
    ADD COLUMN `block_id` VARCHAR(50) DEFAULT NULL
    AFTER `site_id`;

CREATE INDEX `idx_water_block` ON `water_records` (`modx_user_id`, `block_id`);


-- ── crop_info — richer crop tracking ─────────────────────────────────────────

ALTER TABLE `crop_info`
    ADD COLUMN `variety`       VARCHAR(50)  DEFAULT NULL AFTER `name`,
    ADD COLUMN `planting_date` DATE         DEFAULT NULL AFTER `variety`,
    ADD COLUMN `harvest_date`  DATE         DEFAULT NULL AFTER `planting_date`,
    ADD COLUMN `status`        VARCHAR(20)  DEFAULT 'active' AFTER `harvest_date`,
    ADD COLUMN `notes`         TEXT         DEFAULT NULL AFTER `status`;

ALTER TABLE `crop_info`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;