-- ============================================================
-- Migration 001: Config tables for data-driven business rules
-- Target DB: gigawattinc_quote
-- Run as: mysql -u greensolarcopy -p gigawattinc_quote < 001_config_tables.sql
-- ============================================================

-- Snow-load regions (replaces hardcoded JS list in quoteGHL.php)
CREATE TABLE IF NOT EXISTS `snow_load_regions` (
  `id`         INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `match_type` ENUM('state','city') NOT NULL,
  `state`      CHAR(2)         NULL     COMMENT '2-letter abbreviation, used when match_type=state',
  `city`       VARCHAR(100)    NULL     COMMENT 'City name (lowercase), used when match_type=city',
  `notes`      VARCHAR(255)    NULL,
  `active`     TINYINT(1)      NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_state` (`state`),
  KEY `idx_city`  (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PLA (Point Load Analysis) regions (replaces hardcoded county/city list)
CREATE TABLE IF NOT EXISTS `pla_regions` (
  `id`     INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `state`  CHAR(2)         NOT NULL,
  `county` VARCHAR(100)    NULL,
  `city`   VARCHAR(100)    NULL,
  `notes`  VARCHAR(255)    NULL,
  `active` TINYINT(1)      NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sales rep GHL user-ID → name/email mapping (replaces hardcoded switch in quoteGHL.php)
CREATE TABLE IF NOT EXISTS `sales_rep_map` (
  `ghl_user_id` VARCHAR(40)  NOT NULL,
  `rep_name`    VARCHAR(100) NOT NULL,
  `rep_email`   VARCHAR(120) NOT NULL,
  `active`      TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (`ghl_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- State tax configuration (documents which table + lookup method each state uses)
CREATE TABLE IF NOT EXISTS `state_tax_rules` (
  `state`      CHAR(2)                              NOT NULL,
  `lookup_by`  ENUM('zip','city','none')             NOT NULL DEFAULT 'zip',
  `table_name` VARCHAR(64)                           NOT NULL,
  `stored_as`  ENUM('percent','decimal','auto')      NOT NULL DEFAULT 'auto',
  `active`     TINYINT(1)                            NOT NULL DEFAULT 1,
  PRIMARY KEY (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;