CREATE DATABASE IF NOT EXISTS prediksicabe
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE prediksicabe;

CREATE TABLE IF NOT EXISTS commodities (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_commodities_slug (slug)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS markets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(50) NOT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_markets_slug (slug)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS price_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  commodity_id BIGINT UNSIGNED NOT NULL,
  market_id BIGINT UNSIGNED NOT NULL,
  price_date DATE NOT NULL,
  price DECIMAL(15,2) NOT NULL,
  source VARCHAR(100) NOT NULL DEFAULT 'csv_import',
  scraping_log_id BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_price_history_observation (commodity_id, market_id, price_date),
  KEY idx_price_history_date (price_date),
  KEY idx_price_history_commodity_market (commodity_id, market_id),
  CONSTRAINT fk_price_history_commodity FOREIGN KEY (commodity_id) REFERENCES commodities(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_price_history_market FOREIGN KEY (market_id) REFERENCES markets(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS scraping_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  status ENUM('running', 'success', 'failed') NOT NULL,
  records_inserted INT UNSIGNED NOT NULL DEFAULT 0,
  records_updated INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  source VARCHAR(100) NOT NULL DEFAULT 'manual',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_scraping_logs_status (status),
  KEY idx_scraping_logs_started_at (started_at)
) ENGINE=InnoDB;

ALTER TABLE price_history
  ADD KEY idx_price_history_scraping_log (scraping_log_id),
  ADD CONSTRAINT fk_price_history_scraping_log
    FOREIGN KEY (scraping_log_id) REFERENCES scraping_logs(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS preprocessing_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dataset_source VARCHAR(100) NOT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  status ENUM('pending', 'running', 'success', 'failed') NOT NULL,
  rows_before INT UNSIGNED NOT NULL DEFAULT 0,
  rows_after INT UNSIGNED NOT NULL DEFAULT 0,
  parameters_json JSON NULL,
  output_path VARCHAR(500) NULL,
  error_message TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_preprocessing_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS training_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  commodity_id BIGINT UNSIGNED NOT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  status ENUM('pending', 'running', 'success', 'failed') NOT NULL,
  seed INT NULL,
  train_rows INT UNSIGNED NOT NULL DEFAULT 0,
  test_rows INT UNSIGNED NOT NULL DEFAULT 0,
  metrics_json JSON NULL,
  history_path VARCHAR(500) NULL,
  prediction_result_path VARCHAR(500) NULL,
  error_message TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_training_status (status),
  CONSTRAINT fk_training_commodity FOREIGN KEY (commodity_id) REFERENCES commodities(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS model_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  commodity_id BIGINT UNSIGNED NOT NULL,
  version VARCHAR(80) NOT NULL,
  model_path VARCHAR(500) NOT NULL,
  scaler_path VARCHAR(500) NOT NULL,
  config_path VARCHAR(500) NULL,
  metrics_json JSON NULL,
  training_run_id BIGINT UNSIGNED NULL,
  is_active BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_model_version (commodity_id, version),
  KEY idx_model_active (commodity_id, is_active),
  CONSTRAINT fk_model_commodity FOREIGN KEY (commodity_id) REFERENCES commodities(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_model_training FOREIGN KEY (training_run_id) REFERENCES training_runs(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS predictions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  model_version_id BIGINT UNSIGNED NULL,
  commodity_id BIGINT UNSIGNED NOT NULL,
  market_id BIGINT UNSIGNED NOT NULL,
  prediction_date DATE NOT NULL,
  forecast_date DATE NOT NULL,
  predicted_price DECIMAL(15,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_prediction (commodity_id, market_id, prediction_date, forecast_date),
  CONSTRAINT fk_prediction_model FOREIGN KEY (model_version_id) REFERENCES model_versions(id) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_prediction_commodity FOREIGN KEY (commodity_id) REFERENCES commodities(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_prediction_market FOREIGN KEY (market_id) REFERENCES markets(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT INTO commodities (name, slug) VALUES
  ('Cabai Rawit', 'rawit'),
  ('Cabai Merah Besar', 'merah'),
  ('Cabai Merah Keriting', 'keriting')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO markets (name, slug, latitude, longitude) VALUES
  ('Pasar Tanjung', 'pasar_tanjung', -8.1738152, 113.6954052),
  ('Pasar Kreongan', 'pasar_kreongan', -8.1556968, 113.7008754),
  ('Pasar Tegal Besar', 'pasar_tegal_besar', -8.1926152, 113.6987226),
  ('Pasar Mangli', 'pasar_mangli', -8.1902978, 113.6493539),
  ('Pasar Wirolegi', 'pasar_wirolegi', -8.1872926, 113.7431422)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  latitude = VALUES(latitude),
  longitude = VALUES(longitude);