-- =====================================================================
-- OgaSite database schema — Phase 1 foundation
-- MySQL 8 / MariaDB 10.6+ · utf8mb4
--
-- Conventions
--  * Every tenant table has company_id; every query MUST filter by it.
--  * Money is stored as integer kobo (BIGINT/INT), never floats.
--  * Times are DATETIME in WAT (the pool sets the session time zone to +01:00).
--  * Soft delete with deleted_at where records may be restored or audited.
--
-- Safe to re-run: every statement uses IF NOT EXISTS.
-- Apply with:  npm run db:migrate   (or import in phpMyAdmin)
-- =====================================================================

CREATE TABLE IF NOT EXISTS companies (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(150) NOT NULL,
  email           VARCHAR(190) NULL,
  phone           VARCHAR(30)  NULL,
  address         VARCHAR(255) NULL,
  city            VARCHAR(80)  NULL,
  state           VARCHAR(80)  NULL,
  rc_number       VARCHAR(30)  NULL,
  logo_url        VARCHAR(500) NULL,
  plan            ENUM('trial','starter','business','enterprise') NOT NULL DEFAULT 'trial',
  status          ENUM('active','suspended') NOT NULL DEFAULT 'active',
  trial_ends_at   DATETIME NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id        INT UNSIGNED NOT NULL,
  name              VARCHAR(120) NOT NULL,
  email             VARCHAR(190) NOT NULL,           -- unique across the platform (login id)
  phone             VARCHAR(30)  NULL,
  password_hash     VARCHAR(100) NOT NULL,
  role              ENUM('owner','manager','technician','storekeeper','accountant') NOT NULL,
  status            ENUM('active','disabled') NOT NULL DEFAULT 'active',
  hourly_rate_kobo  INT UNSIGNED NOT NULL DEFAULT 0,  -- labour cost for job costing
  must_change_password TINYINT(1) NOT NULL DEFAULT 0, -- set when an admin creates the account
  last_login_at     DATETIME NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_company (company_id),
  CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Refresh tokens: only a SHA-256 hash is stored; rotated on every refresh.
CREATE TABLE IF NOT EXISTS refresh_tokens (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  token_hash  CHAR(64) NOT NULL,
  expires_at  DATETIME NOT NULL,
  revoked_at  DATETIME NULL,
  user_agent  VARCHAR(255) NULL,
  ip          VARCHAR(45)  NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_refresh_hash (token_hash),
  KEY idx_refresh_user (user_id),
  CONSTRAINT fk_refresh_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Password reset codes (6 digits, hashed, 15-minute life, max 5 attempts).
CREATE TABLE IF NOT EXISTS password_resets (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  code_hash   CHAR(64) NOT NULL,
  expires_at  DATETIME NOT NULL,
  attempts    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  used_at     DATETIME NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_resets_user (user_id),
  CONSTRAINT fk_resets_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 customers (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id    INT UNSIGNED NOT NULL,
  type          ENUM('individual','business') NOT NULL DEFAULT 'individual',
  name          VARCHAR(150) NOT NULL,
  contact_name  VARCHAR(120) NULL,   -- for business customers
  phone         VARCHAR(30)  NULL,
  email         VARCHAR(190) NULL,
  address       VARCHAR(255) NULL,   -- billing address
  notes         TEXT NULL,
  created_by    INT UNSIGNED NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at    DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_customers_company (company_id, deleted_at),
  KEY idx_customers_name (company_id, name),
  CONSTRAINT fk_customers_company FOREIGN KEY (company_id) REFERENCES companies (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A customer can have many sites (where the work happens).
CREATE TABLE IF NOT EXISTS customer_sites (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id     INT UNSIGNED NOT NULL,
  customer_id    INT UNSIGNED NOT NULL,
  label          VARCHAR(120) NOT NULL,   -- e.g. "Head office", "Duplex — GRA"
  address        VARCHAR(255) NOT NULL,
  landmark       VARCHAR(190) NULL,
  city           VARCHAR(80)  NULL,
  state          VARCHAR(80)  NULL,
  lat            DECIMAL(10,7) NULL,
  lng            DECIMAL(10,7) NULL,
  contact_name   VARCHAR(120) NULL,
  contact_phone  VARCHAR(30)  NULL,
  access_notes   VARCHAR(500) NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at     DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_sites_customer (company_id, customer_id, deleted_at),
  CONSTRAINT fk_sites_company FOREIGN KEY (company_id) REFERENCES companies (id),
  CONSTRAINT fk_sites_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo requests from the marketing website (not tenant data).
CREATE TABLE IF NOT EXISTS leads (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(120) NOT NULL,
  company     VARCHAR(150) NOT NULL,
  phone       VARCHAR(30)  NOT NULL,
  email       VARCHAR(190) NOT NULL,
  industry    VARCHAR(60)  NOT NULL,
  team_size   VARCHAR(10)  NOT NULL,
  message     TEXT NULL,
  source      VARCHAR(60)  NULL,
  status      ENUM('new','contacted','won','lost') NOT NULL DEFAULT 'new',
  ip          VARCHAR(45)  NULL,
  user_agent  VARCHAR(255) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_leads_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Who changed what, per company.
CREATE TABLE IF NOT EXISTS audit_logs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id  INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NULL,
  entity      VARCHAR(40) NOT NULL,     -- e.g. 'customer', 'user', 'company'
  entity_id   INT UNSIGNED NULL,
  action      VARCHAR(40) NOT NULL,     -- e.g. 'create', 'update', 'delete', 'login'
  changes     JSON NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_company (company_id, created_at),
  KEY idx_audit_entity (company_id, entity, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;