-- Growlytics Plant Growth Tracker — Full Schema (fresh install)
CREATE DATABASE IF NOT EXISTS growlytics
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE growlytics;

-- ── Users ──────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  email            VARCHAR(255) NOT NULL UNIQUE,
  password_hash    VARCHAR(255) NOT NULL,
  display_name     VARCHAR(100) NOT NULL,
  profile_picture  VARCHAR(255),
  totp_secret      VARCHAR(255),
  totp_enabled     TINYINT(1) DEFAULT 0,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Plants ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS plants (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT NOT NULL,
  name          VARCHAR(100) NOT NULL,
  species       VARCHAR(100),
  variety       VARCHAR(100),
  start_date    DATE,
  pot_size      VARCHAR(50),
  soil_type     VARCHAR(100),
  location      VARCHAR(100),
  is_public     TINYINT(1) DEFAULT 1,
  notes         TEXT,
  cover_image   VARCHAR(255),
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Growth measurements ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS growth_logs (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  plant_id          INT NOT NULL,
  log_date          DATE NOT NULL,
  height_cm         DECIMAL(8,2),
  width_cm          DECIMAL(8,2),
  leaf_count        INT,
  stem_diameter_mm  DECIMAL(6,2),
  image_path        VARCHAR(255),
  notes             TEXT,
  created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Watering events ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS watering_logs (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  plant_id    INT NOT NULL,
  log_date    DATE NOT NULL,
  amount_ml   INT,
  water_type  VARCHAR(50),
  ph_level    DECIMAL(4,2),
  notes       TEXT,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Fertilizer applications ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fertilizer_logs (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  plant_id         INT NOT NULL,
  log_date         DATE NOT NULL,
  fertilizer_name  VARCHAR(100),
  fertilizer_type  VARCHAR(50),
  npk_ratio        VARCHAR(20),
  amount_ml        DECIMAL(8,2),
  dilution_ratio   VARCHAR(50),
  notes            TEXT,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Light exposure ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS light_logs (
  id                      INT AUTO_INCREMENT PRIMARY KEY,
  plant_id                INT NOT NULL,
  log_date                DATE NOT NULL,
  sunlight_hours          DECIMAL(4,2),
  sunlight_intensity      VARCHAR(50),
  artificial_light_hours  DECIMAL(4,2),
  light_type              VARCHAR(50),
  light_color             VARCHAR(50),
  light_watts             INT,
  distance_cm             INT,
  notes                   TEXT,
  created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Health assessments ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS health_logs (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  plant_id         INT NOT NULL,
  log_date         DATE NOT NULL,
  health_score     TINYINT,
  symptoms         TEXT,
  treatment        TEXT,
  pest_issues      VARCHAR(255),
  disease_issues   VARCHAR(255),
  notes            TEXT,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Environmental conditions ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS environment_logs (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  plant_id          INT NOT NULL,
  log_date          DATE NOT NULL,
  temperature_c     DECIMAL(5,2),
  humidity_percent  TINYINT UNSIGNED,
  co2_ppm           INT,
  air_circulation   VARCHAR(50),
  notes             TEXT,
  created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── AI Analyses ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ai_analyses (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  plant_id       INT NOT NULL,
  mode           VARCHAR(20) NOT NULL DEFAULT 'full',
  content        LONGTEXT NOT NULL,
  input_tokens   INT,
  output_tokens  INT,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Plant intelligence catalog ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS plant_catalog (
  id                         INT AUTO_INCREMENT PRIMARY KEY,
  slug                       VARCHAR(120) NOT NULL UNIQUE,
  common_name                VARCHAR(120) NOT NULL,
  scientific_name            VARCHAR(160),
  plant_type                 VARCHAR(80),
  difficulty                 VARCHAR(40),
  light_needs                VARCHAR(120),
  water_needs                VARCHAR(120),
  humidity_preference        VARCHAR(120),
  ideal_temp_c_min           DECIMAL(5,2),
  ideal_temp_c_max           DECIMAL(5,2),
  ideal_ph_min               DECIMAL(4,2),
  ideal_ph_max               DECIMAL(4,2),
  usda_zone_min              VARCHAR(10),
  usda_zone_max              VARCHAR(10),
  growth_rate                VARCHAR(60),
  mature_height_cm_min       INT,
  mature_height_cm_max       INT,
  days_to_maturity_min       INT,
  days_to_maturity_max       INT,
  pet_safe                   TINYINT(1) DEFAULT 1,
  care_summary               TEXT,
  care_tips                  TEXT,
  common_issues              TEXT,
  expected_features          TEXT,
  company_opportunity_notes  TEXT,
  popularity_score           INT DEFAULT 0,
  image_gradient             VARCHAR(120),
  created_at                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Grow zones & regional recommendations ─────────────────────────────────
CREATE TABLE IF NOT EXISTS grow_zones (
  id                       INT AUTO_INCREMENT PRIMARY KEY,
  zone_code                VARCHAR(10) NOT NULL UNIQUE,
  label                    VARCHAR(120) NOT NULL,
  climate_summary          TEXT,
  frost_window             VARCHAR(120),
  growing_season           VARCHAR(120),
  rainfall_pattern         VARCHAR(120),
  suitable_plants          TEXT,
  challenges               TEXT,
  recommended_tasks        TEXT,
  created_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at               TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Special treatments ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS treatments (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  plant_id        INT NOT NULL,
  log_date        DATE NOT NULL,
  treatment_type  VARCHAR(100),
  description     TEXT,
  notes           TEXT,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Seed: plant intelligence catalog ──────────────────────────────────────
INSERT INTO plant_catalog (
  slug, common_name, scientific_name, plant_type, difficulty, light_needs, water_needs,
  humidity_preference, ideal_temp_c_min, ideal_temp_c_max, ideal_ph_min, ideal_ph_max,
  usda_zone_min, usda_zone_max, growth_rate, mature_height_cm_min, mature_height_cm_max,
  days_to_maturity_min, days_to_maturity_max, pet_safe, care_summary, care_tips,
  common_issues, expected_features, company_opportunity_notes, popularity_score, image_gradient
) VALUES
(
  'basil', 'Basil', 'Ocimum basilicum', 'Culinary Herb', 'Easy', 'Full sun to bright grow light',
  'Keep evenly moist but not soggy', 'Moderate humidity', 18, 30, 6.0, 7.5, '9', '12',
  'Fast', 25, 60, 40, 75, 1,
  'Fast-growing herb that rewards frequent harvesting, warmth, and consistent moisture.',
  'Pinch top growth often, avoid cold drafts, and harvest before flowering for best flavor.',
  'Cold shock, downy mildew, overwatering, legginess from weak light.',
  'Fragrant leaves, rapid regrowth, compact bushy habit when pruned regularly.',
  'Great anchor species for seed brands, kitchen-garden kits, and beginner education.',
  95, 'linear-gradient(135deg,#24553a,#6dc28c)'
),
(
  'tomato', 'Tomato', 'Solanum lycopersicum', 'Fruit Crop', 'Moderate', 'Full sun, 6-8+ hours',
  'Deep watering with dry-back between irrigations', 'Moderate humidity with good airflow', 18, 29, 6.0, 6.8, '3', '11',
  'Fast', 90, 240, 60, 100, 0,
  'Heavy-feeding fruiting plant that thrives with strong light, staking, airflow, and consistent watering.',
  'Support early, prune to desired structure, and maintain stable moisture to reduce blossom-end rot and cracking.',
  'Blossom-end rot, hornworms, early blight, split fruit, nutrient swings.',
  'Fast vegetative growth, flowers followed by heavy fruit set, strong seasonal momentum.',
  'Ideal for fertilizer, trellis, irrigation, and disease-management partnerships.',
  98, 'linear-gradient(135deg,#365b2b,#ef7a4f)'
),
(
  'lettuce', 'Lettuce', 'Lactuca sativa', 'Leafy Green', 'Easy', 'Partial sun to bright indirect light',
  'Consistent moisture with cool roots', 'Moderate to high humidity', 10, 24, 6.0, 7.0, '4', '9',
  'Fast', 15, 35, 30, 55, 1,
  'Quick crop for beginners that prefers cooler weather and uniform moisture.',
  'Succession sow every 1-2 weeks, provide afternoon shade in warm regions, and harvest outer leaves early.',
  'Bolting in heat, tip burn, aphids, bitter flavor from stress.',
  'Dense leafy heads or cut-and-come-again harvests with very fast feedback cycles.',
  'Strong candidate for subscription seed boxes and classroom grow programs.',
  86, 'linear-gradient(135deg,#3d6f41,#9adf8d)'
),
(
  'lavender', 'Lavender', 'Lavandula angustifolia', 'Aromatic Perennial', 'Moderate', 'Full sun',
  'Low to moderate, prefers drying out between waterings', 'Low humidity', 12, 30, 6.5, 8.0, '5', '9',
  'Moderate', 35, 90, 90, 150, 1,
  'Mediterranean herb prized for fragrance, flowers, and drought tolerance once established.',
  'Use very well-draining soil, avoid wet crowns, and prune after flowering to keep plants compact.',
  'Root rot, woody stems from skipped pruning, poor flowering in shade.',
  'Silvery foliage, fragrant blooms, pollinator appeal, low-water landscaping value.',
  'High value for ornamental, wellness, and pollinator-focused product lines.',
  80, 'linear-gradient(135deg,#5a4d8d,#9b90d8)'
),
(
  'snake-plant', 'Snake Plant', 'Dracaena trifasciata', 'Houseplant', 'Easy', 'Low to bright indirect light',
  'Low water, allow full dry-back', 'Average indoor humidity', 16, 32, 5.5, 7.5, '9', '12',
  'Slow', 25, 120, 180, 365, 0,
  'Resilient indoor plant that tolerates neglect and low light better than most foliage plants.',
  'Use a fast-draining mix, water sparingly, and avoid placing it in consistently dark corners if you want faster growth.',
  'Root rot, mushy leaves from overwatering, stalled growth in very dim rooms.',
  'Architectural upright foliage, strong beginner appeal, excellent office and interior use.',
  'Great fit for office, hospitality, and low-maintenance subscription offerings.',
  90, 'linear-gradient(135deg,#21453d,#8ecf7c)'
),
(
  'pepper', 'Pepper', 'Capsicum annuum', 'Fruit Crop', 'Moderate', 'Full sun, high PPFD indoors',
  'Even moisture with warm root zone', 'Moderate humidity', 20, 30, 6.0, 6.8, '4', '11',
  'Moderate', 45, 120, 70, 110, 1,
  'Warm-season crop that responds strongly to heat, light intensity, and balanced feeding.',
  'Keep nights warm, avoid overwatering during cool spells, and support branches once fruit load increases.',
  'Blossom drop from heat stress, aphids, calcium issues, slow growth in cool conditions.',
  'Compact branching, glossy foliage, abundant flowers, colorful fruit progression.',
  'Excellent for consumer seed kits, indoor grow lights, and culinary content partnerships.',
  88, 'linear-gradient(135deg,#37522f,#d95f49)'
)
ON DUPLICATE KEY UPDATE
  common_name = VALUES(common_name),
  scientific_name = VALUES(scientific_name),
  plant_type = VALUES(plant_type),
  difficulty = VALUES(difficulty),
  light_needs = VALUES(light_needs),
  water_needs = VALUES(water_needs),
  humidity_preference = VALUES(humidity_preference),
  ideal_temp_c_min = VALUES(ideal_temp_c_min),
  ideal_temp_c_max = VALUES(ideal_temp_c_max),
  ideal_ph_min = VALUES(ideal_ph_min),
  ideal_ph_max = VALUES(ideal_ph_max),
  usda_zone_min = VALUES(usda_zone_min),
  usda_zone_max = VALUES(usda_zone_max),
  growth_rate = VALUES(growth_rate),
  mature_height_cm_min = VALUES(mature_height_cm_min),
  mature_height_cm_max = VALUES(mature_height_cm_max),
  days_to_maturity_min = VALUES(days_to_maturity_min),
  days_to_maturity_max = VALUES(days_to_maturity_max),
  pet_safe = VALUES(pet_safe),
  care_summary = VALUES(care_summary),
  care_tips = VALUES(care_tips),
  common_issues = VALUES(common_issues),
  expected_features = VALUES(expected_features),
  company_opportunity_notes = VALUES(company_opportunity_notes),
  popularity_score = VALUES(popularity_score),
  image_gradient = VALUES(image_gradient);

-- ── Seed: grow zones ──────────────────────────────────────────────────────
INSERT INTO grow_zones (
  zone_code, label, climate_summary, frost_window, growing_season, rainfall_pattern,
  suitable_plants, challenges, recommended_tasks
) VALUES
(
  '3b', 'Zone 3b · Short cool season',
  'Cold winters, brief summers, and strong need for frost-aware crop planning.',
  'Late May to early September', 'Very short outdoor season', 'Moderate rainfall, cool nights',
  'Lettuce, kale, peas, potatoes, hardy herbs.',
  'Late frosts, slow soil warming, compressed fruiting season.',
  'Start indoors early, use row cover, favor fast-maturing cultivars.'
),
(
  '5a', 'Zone 5a · Four-season growing',
  'Distinct seasons with cold winters and a dependable but moderate summer window.',
  'Mid May to early October', 'Medium-length season', 'Balanced rainfall with spring surges',
  'Tomato, lettuce, basil, pepper, lavender.',
  'Spring swings, fungal pressure after rain, short shoulder seasons.',
  'Use transplants, stagger plantings, and monitor disease pressure after wet spells.'
),
(
  '6b', 'Zone 6b · Productive mixed-climate gardens',
  'Flexible home-garden climate with enough warmth for many vegetables and herbs.',
  'Late April to mid October', 'Long enough for most annual crops', 'Moderate rainfall',
  'Tomato, pepper, basil, lettuce, lavender, cucumbers.',
  'Heat spikes, mildew during humid spells, irregular watering stress.',
  'Mulch beds, feed heavy fruiters consistently, and plant pollinator companions.'
),
(
  '7b', 'Zone 7b · Warm temperate balance',
  'Long season with warm summers and relatively mild winters.',
  'Mid April to late October', 'Long outdoor season', 'Moderate to variable rainfall',
  'Tomato, pepper, basil, lavender, rosemary, figs.',
  'Summer drought, pest pressure, overgrowth if pruning is delayed.',
  'Succession sow greens in shoulder seasons and switch to heat-tolerant crops in peak summer.'
),
(
  '8a', 'Zone 8a · Mild winters, long season',
  'Strong flexibility for herbs, fruiting crops, and perennials with a long warm season.',
  'Late March to mid November', 'Long and forgiving', 'Variable rainfall with summer dry periods',
  'Tomato, pepper, basil, lavender, citrus in protected microclimates.',
  'Heat stress, drought, fungal disease in humid stretches.',
  'Prioritize irrigation planning and rotate crops to manage disease carryover.'
),
(
  '9b', 'Zone 9b · Heat-loving grower territory',
  'Hot summers and mild winters favor long harvest windows and year-round planning.',
  'Rare frost, usually December to February only', 'Very long season', 'Dry to mixed depending on region',
  'Basil, tomato, pepper, citrus, bougainvillea, snake plant outdoors in protected sites.',
  'Sun scorch, water demand, blossom drop during extreme heat.',
  'Use shade cloth during heat waves and lean into cool-season greens in winter.'
),
(
  '10a', 'Zone 10a · Subtropical abundance',
  'Minimal frost and extended production windows support tropical and subtropical crops.',
  'Rare and light frost', 'Near year-round', 'Often humid or coastal',
  'Pepper, basil, tropical herbs, citrus, banana, houseplants outdoors.',
  'Humidity-driven disease, salt air in coastal areas, pest persistence.',
  'Increase airflow, scout weekly, and treat seasons by wet/dry patterns rather than frost dates.'
),
(
  '11a', 'Zone 11a · Tropical year-round growing',
  'Frost-free climate with continuous production potential for warm-loving plants.',
  'No regular frost', 'Year-round', 'Humid or monsoonal depending on region',
  'Basil, pepper, tropical ornamentals, ginger, turmeric, banana.',
  'High disease pressure, nutrient leaching, continuous pest cycles.',
  'Build drainage, rotate beds aggressively, and schedule preventive scouting year-round.'
)
ON DUPLICATE KEY UPDATE
  label = VALUES(label),
  climate_summary = VALUES(climate_summary),
  frost_window = VALUES(frost_window),
  growing_season = VALUES(growing_season),
  rainfall_pattern = VALUES(rainfall_pattern),
  suitable_plants = VALUES(suitable_plants),
  challenges = VALUES(challenges),
  recommended_tasks = VALUES(recommended_tasks);
