-- =============================================================
-- GRC Platform — Master Schema
-- Sets up a complete, fresh database from scratch.
--
-- Usage:
--   mysql -u <user> -p <dbname> < migrations/schema.sql
--
-- After running:
--   1. Visit /#/sysadmin/login to create the first system admin
--   2. Tenant superadmin: admin@grcplatform.local / Adm1n@GRC2024!
--      (change immediately after first login)
-- =============================================================

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- =============================================================
-- SYSTEM ADMINS  (defined first — referenced by promo_codes FK)
-- =============================================================

CREATE TABLE IF NOT EXISTS system_admins (
  id            CHAR(36)     NOT NULL,
  email         VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name          VARCHAR(255) NOT NULL,
  is_active     BOOLEAN      NOT NULL DEFAULT TRUE,
  mfa_secret    VARCHAR(255)          DEFAULT NULL,
  mfa_enabled   BOOLEAN      NOT NULL DEFAULT FALSE,
  created_by    CHAR(36)              DEFAULT NULL,
  last_login_at DATETIME              DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT NOW(),
  updated_at    DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_sysadmin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sysadmin_refresh_tokens (
  id         CHAR(36)     NOT NULL,
  admin_id   CHAR(36)     NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME     NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_sysadmin_token (token_hash),
  INDEX idx_sysadmin_token_admin (admin_id),
  CONSTRAINT fk_srt_admin FOREIGN KEY (admin_id) REFERENCES system_admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- No seed row — first system admin is created via the /#/sysadmin/login setup flow.

-- =============================================================
-- TENANCY & IDENTITY
-- =============================================================

CREATE TABLE IF NOT EXISTS tenants (
  id                             CHAR(36)     NOT NULL DEFAULT (UUID()),
  name                           VARCHAR(255) NOT NULL,
  slug                           VARCHAR(100) NOT NULL,
  plan                           ENUM('starter','professional','enterprise') NOT NULL DEFAULT 'starter',
  region                         VARCHAR(100),
  industry                       VARCHAR(100),
  logo_url                       TEXT,
  settings                       JSON,
  status                         ENUM('active','suspended','trial') NOT NULL DEFAULT 'trial',
  is_active                      BOOLEAN      NOT NULL DEFAULT TRUE,
  trial_ends_at                  DATETIME,
  billing_paid_until             DATETIME,
  stripe_customer_id             VARCHAR(255),
  stripe_default_payment_method  VARCHAR(255),
  billing_currency               CHAR(3)      NOT NULL DEFAULT 'usd',
  billing_price_per_month_cents  INT UNSIGNED NOT NULL DEFAULT 50000,
  created_at                     DATETIME     NOT NULL DEFAULT NOW(),
  updated_at                     DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id                     CHAR(36)         NOT NULL DEFAULT (UUID()),
  tenant_id              CHAR(36)         NOT NULL,
  email                  VARCHAR(255)     NOT NULL,
  password_hash          VARCHAR(255)     NOT NULL,
  first_name             VARCHAR(100),
  last_name              VARCHAR(100),
  role                   ENUM('superadmin','admin','compliance_officer','risk_manager','auditor','viewer') NOT NULL DEFAULT 'viewer',
  mfa_secret             VARCHAR(100),
  mfa_enabled            BOOLEAN          NOT NULL DEFAULT FALSE,
  failed_login_attempts  TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until           DATETIME,
  last_login_at          DATETIME,
  is_active              BOOLEAN          NOT NULL DEFAULT TRUE,
  created_at             DATETIME         NOT NULL DEFAULT NOW(),
  updated_at             DATETIME         NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_tenant_email (tenant_id, email),
  INDEX idx_email (email),
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
  id         CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id    CHAR(36)     NOT NULL,
  tenant_id  CHAR(36)     NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  expires_at DATETIME     NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_token (token_hash),
  INDEX idx_user_tenant (user_id, tenant_id),
  CONSTRAINT fk_sessions_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 password_reset_tokens (
  id          CHAR(36)     NOT NULL DEFAULT (UUID()),
  user_id     CHAR(36)     NOT NULL,
  tenant_id   CHAR(36)     NOT NULL,
  token_hash  VARCHAR(128) NOT NULL,
  expires_at  DATETIME     NOT NULL,
  consumed_at DATETIME,
  created_at  DATETIME     NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_password_reset_token_hash (token_hash),
  INDEX idx_password_reset_user    (user_id),
  INDEX idx_password_reset_expires (expires_at),
  CONSTRAINT fk_password_reset_user   FOREIGN KEY (user_id)   REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_password_reset_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     CHAR(36)        NOT NULL,
  user_id       CHAR(36),
  action        VARCHAR(100)    NOT NULL,
  resource_type VARCHAR(100),
  resource_id   CHAR(36),
  old_values    JSON,
  new_values    JSON,
  ip_address    VARCHAR(45),
  created_at    DATETIME        NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  INDEX idx_audit_logs_tenant_created (tenant_id, created_at),
  INDEX idx_audit_logs_resource       (resource_type, resource_id),
  INDEX idx_audit_logs_user           (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- BILLING
-- =============================================================

CREATE TABLE IF NOT EXISTS billing_payments (
  id                       CHAR(36)          NOT NULL DEFAULT (UUID()),
  tenant_id                CHAR(36)          NOT NULL,
  user_id                  CHAR(36),
  stripe_payment_intent_id VARCHAR(255)      NOT NULL,
  stripe_charge_id         VARCHAR(255),
  amount_cents             INT UNSIGNED      NOT NULL,
  currency                 CHAR(3)           NOT NULL DEFAULT 'usd',
  months_purchased         SMALLINT UNSIGNED NOT NULL,
  status                   ENUM('requires_payment_method','requires_confirmation','requires_action','processing','succeeded','canceled','failed') NOT NULL,
  failure_reason           TEXT,
  receipt_url              TEXT,
  paid_until_before        DATETIME,
  paid_until_after         DATETIME,
  paid_at                  DATETIME,
  created_at               DATETIME          NOT NULL DEFAULT NOW(),
  updated_at               DATETIME          NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_billing_payment_intent (stripe_payment_intent_id),
  INDEX idx_billing_tenant_created (tenant_id, created_at),
  CONSTRAINT fk_billing_payments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_billing_payments_user   FOREIGN KEY (user_id)   REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promo_codes (
  id               CHAR(36)          NOT NULL DEFAULT (UUID()),
  code             VARCHAR(64)       NOT NULL,
  months_granted   SMALLINT UNSIGNED NOT NULL,
  max_redemptions  INT UNSIGNED      NOT NULL DEFAULT 1,
  redemption_count INT UNSIGNED      NOT NULL DEFAULT 0,
  expires_at       DATETIME,
  is_active        BOOLEAN           NOT NULL DEFAULT TRUE,
  notes            TEXT,
  created_by       CHAR(36),
  created_at       DATETIME          NOT NULL DEFAULT NOW(),
  updated_at       DATETIME          NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_promo_code (code),
  INDEX idx_promo_active (is_active, expires_at),
  CONSTRAINT fk_promo_created_by FOREIGN KEY (created_by) REFERENCES system_admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promo_code_redemptions (
  id                CHAR(36)          NOT NULL DEFAULT (UUID()),
  promo_code_id     CHAR(36)          NOT NULL,
  tenant_id         CHAR(36)          NOT NULL,
  user_id           CHAR(36),
  code              VARCHAR(64)       NOT NULL,
  months_granted    SMALLINT UNSIGNED NOT NULL,
  paid_until_before DATETIME,
  paid_until_after  DATETIME,
  redeemed_at       DATETIME          NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  INDEX idx_promo_redemptions_tenant (tenant_id, redeemed_at),
  INDEX idx_promo_redemptions_code   (promo_code_id),
  CONSTRAINT fk_promo_redemption_code   FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id) ON DELETE CASCADE,
  CONSTRAINT fk_promo_redemption_tenant FOREIGN KEY (tenant_id)     REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_promo_redemption_user   FOREIGN KEY (user_id)       REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- COMPLIANCE FRAMEWORKS
-- =============================================================

CREATE TABLE IF NOT EXISTS regulatory_frameworks (
  id             CHAR(36)     NOT NULL DEFAULT (UUID()),
  code           VARCHAR(50)  NOT NULL,
  name           VARCHAR(255) NOT NULL,
  description    TEXT,
  version        VARCHAR(50),
  issuing_body   VARCHAR(255),
  regions        JSON,
  industries     JSON,
  effective_date DATE,
  is_global      BOOLEAN      NOT NULL DEFAULT FALSE,
  created_at     DATETIME     NOT NULL DEFAULT NOW(),
  updated_at     DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_fw_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS framework_requirements (
  id               CHAR(36)     NOT NULL DEFAULT (UUID()),
  framework_id     CHAR(36)     NOT NULL,
  parent_id        CHAR(36),
  reference_code   VARCHAR(100) NOT NULL,
  title            VARCHAR(500) NOT NULL,
  description      TEXT,
  guidance         TEXT,
  requirement_type ENUM('mandatory','recommended','optional') NOT NULL DEFAULT 'mandatory',
  category         VARCHAR(255),
  tags             JSON,
  sort_order       INT          NOT NULL DEFAULT 0,
  created_at       DATETIME     NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  INDEX idx_req_framework (framework_id),
  INDEX idx_req_parent    (parent_id),
  CONSTRAINT fk_req_framework FOREIGN KEY (framework_id) REFERENCES regulatory_frameworks(id) ON DELETE CASCADE,
  CONSTRAINT fk_req_parent    FOREIGN KEY (parent_id)    REFERENCES framework_requirements(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tenant_frameworks (
  id           CHAR(36) NOT NULL DEFAULT (UUID()),
  tenant_id    CHAR(36) NOT NULL,
  framework_id CHAR(36) NOT NULL,
  status       ENUM('active','inactive','in_review') NOT NULL DEFAULT 'active',
  target_date  DATE,
  owner_id     CHAR(36),
  notes        TEXT,
  activated_at DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_tenant_framework (tenant_id, framework_id),
  CONSTRAINT fk_tf_tenant    FOREIGN KEY (tenant_id)    REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_tf_framework FOREIGN KEY (framework_id) REFERENCES regulatory_frameworks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- CONTROLS LIBRARY
-- =============================================================

CREATE TABLE IF NOT EXISTS controls (
  id               CHAR(36)     NOT NULL DEFAULT (UUID()),
  tenant_id        CHAR(36)     NOT NULL,
  control_id       VARCHAR(100) NOT NULL,
  title            VARCHAR(500) NOT NULL,
  description      TEXT,
  type             ENUM('preventive','detective','corrective','deterrent','compensating') NOT NULL DEFAULT 'preventive',
  category         VARCHAR(255),
  frequency        ENUM('continuous','daily','weekly','monthly','quarterly','annually','ad_hoc'),
  owner_id         CHAR(36),
  status           ENUM('active','inactive','under_review') NOT NULL DEFAULT 'active',
  effectiveness    ENUM('effective','partially_effective','ineffective','not_tested') NOT NULL DEFAULT 'not_tested',
  automation_level ENUM('manual','semi_automated','automated') NOT NULL DEFAULT 'manual',
  last_tested_at   DATETIME,
  tags             JSON,
  created_at       DATETIME     NOT NULL DEFAULT NOW(),
  updated_at       DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  INDEX idx_controls_tenant_status     (tenant_id, status),
  INDEX idx_controls_tenant_control_id (tenant_id, control_id),
  FULLTEXT INDEX ft_controls           (title, description),
  CONSTRAINT fk_controls_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS control_framework_mappings (
  id             CHAR(36) NOT NULL DEFAULT (UUID()),
  tenant_id      CHAR(36) NOT NULL,
  control_id     CHAR(36) NOT NULL,
  requirement_id CHAR(36) NOT NULL,
  coverage       ENUM('full','partial','none') NOT NULL DEFAULT 'partial',
  notes          TEXT,
  mapped_at      DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_control_req (control_id, requirement_id),
  INDEX idx_cfm_tenant (tenant_id),
  CONSTRAINT fk_cfm_control FOREIGN KEY (control_id)     REFERENCES controls(id) ON DELETE CASCADE,
  CONSTRAINT fk_cfm_req     FOREIGN KEY (requirement_id) REFERENCES framework_requirements(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- RISK REGISTER
-- =============================================================

CREATE TABLE IF NOT EXISTS risk_categories (
  id          CHAR(36)     NOT NULL DEFAULT (UUID()),
  tenant_id   CHAR(36)     NOT NULL,
  name        VARCHAR(255) NOT NULL,
  description TEXT,
  color       VARCHAR(7),
  parent_id   CHAR(36),
  created_at  DATETIME     NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  CONSTRAINT fk_rcat_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_rcat_parent FOREIGN KEY (parent_id) REFERENCES risk_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS risks (
  id                  CHAR(36)         NOT NULL DEFAULT (UUID()),
  tenant_id           CHAR(36)         NOT NULL,
  risk_id             VARCHAR(100)     NOT NULL,
  title               VARCHAR(500)     NOT NULL,
  description         TEXT,
  category_id         CHAR(36),
  owner_id            CHAR(36),
  status              ENUM('open','mitigated','accepted','transferred','closed') NOT NULL DEFAULT 'open',
  likelihood          TINYINT UNSIGNED NOT NULL CHECK (likelihood BETWEEN 1 AND 5),
  impact              TINYINT UNSIGNED NOT NULL CHECK (impact BETWEEN 1 AND 5),
  inherent_score      TINYINT UNSIGNED GENERATED ALWAYS AS (likelihood * impact) STORED,
  residual_likelihood TINYINT UNSIGNED CHECK (residual_likelihood BETWEEN 1 AND 5),
  residual_impact     TINYINT UNSIGNED CHECK (residual_impact BETWEEN 1 AND 5),
  residual_score      TINYINT UNSIGNED GENERATED ALWAYS AS (
                        CASE WHEN residual_likelihood IS NOT NULL AND residual_impact IS NOT NULL
                          THEN residual_likelihood * residual_impact ELSE NULL END
                      ) STORED,
  target_date         DATE,
  treatment           ENUM('mitigate','accept','transfer','avoid'),
  tags                JSON,
  review_date         DATE,
  created_at          DATETIME         NOT NULL DEFAULT NOW(),
  updated_at          DATETIME         NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  INDEX idx_risks_tenant_status  (tenant_id, status),
  INDEX idx_risks_tenant_score   (tenant_id, inherent_score),
  INDEX idx_risks_tenant_risk_id (tenant_id, risk_id),
  FULLTEXT INDEX ft_risks        (title, description),
  CONSTRAINT fk_risks_tenant   FOREIGN KEY (tenant_id)   REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_risks_category FOREIGN KEY (category_id) REFERENCES risk_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS risk_treatments (
  id            CHAR(36)       NOT NULL DEFAULT (UUID()),
  tenant_id     CHAR(36)       NOT NULL,
  risk_id       CHAR(36)       NOT NULL,
  title         VARCHAR(500)   NOT NULL,
  description   TEXT,
  type          ENUM('control','action','acceptance','transfer'),
  control_id    CHAR(36),
  owner_id      CHAR(36),
  status        ENUM('planned','in_progress','completed','cancelled') NOT NULL DEFAULT 'planned',
  due_date      DATE,
  cost_estimate DECIMAL(15,2),
  created_at    DATETIME       NOT NULL DEFAULT NOW(),
  updated_at    DATETIME       NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  CONSTRAINT fk_rt_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_rt_risk   FOREIGN KEY (risk_id)   REFERENCES risks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS risk_control_mappings (
  risk_id    CHAR(36) NOT NULL,
  control_id CHAR(36) NOT NULL,
  tenant_id  CHAR(36) NOT NULL,
  PRIMARY KEY (risk_id, control_id),
  CONSTRAINT fk_rcm_risk    FOREIGN KEY (risk_id)    REFERENCES risks(id) ON DELETE CASCADE,
  CONSTRAINT fk_rcm_control FOREIGN KEY (control_id) REFERENCES controls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- AUDIT MANAGEMENT
-- =============================================================

CREATE TABLE IF NOT EXISTS audits (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  tenant_id       CHAR(36)     NOT NULL,
  audit_id        VARCHAR(100) NOT NULL,
  title           VARCHAR(500) NOT NULL,
  type            ENUM('internal','external','regulatory','certification') NOT NULL DEFAULT 'internal',
  framework_id    CHAR(36),
  lead_auditor_id CHAR(36),
  status          ENUM('planned','in_progress','review','completed','cancelled') NOT NULL DEFAULT 'planned',
  scope           TEXT,
  objectives      TEXT,
  planned_start   DATE,
  planned_end     DATE,
  actual_start    DATE,
  actual_end      DATE,
  opinion         ENUM('clean','qualified','adverse','disclaimer'),
  summary         TEXT,
  created_at      DATETIME     NOT NULL DEFAULT NOW(),
  updated_at      DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  INDEX idx_audits_tenant_status (tenant_id, status),
  CONSTRAINT fk_audits_tenant    FOREIGN KEY (tenant_id)    REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_audits_framework FOREIGN KEY (framework_id) REFERENCES regulatory_frameworks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_findings (
  id                  CHAR(36)     NOT NULL DEFAULT (UUID()),
  tenant_id           CHAR(36)     NOT NULL,
  audit_id            CHAR(36)     NOT NULL,
  finding_id          VARCHAR(100) NOT NULL,
  title               VARCHAR(500) NOT NULL,
  description         TEXT,
  severity            ENUM('critical','high','medium','low','informational') NOT NULL DEFAULT 'medium',
  status              ENUM('open','in_remediation','remediated','accepted','closed') NOT NULL DEFAULT 'open',
  control_id          CHAR(36),
  requirement_id      CHAR(36),
  recommendation      TEXT,
  management_response TEXT,
  owner_id            CHAR(36),
  due_date            DATE,
  closed_at           DATETIME,
  created_at          DATETIME     NOT NULL DEFAULT NOW(),
  updated_at          DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  INDEX idx_af_tenant_severity (tenant_id, severity),
  INDEX idx_af_tenant_status   (tenant_id, status),
  INDEX idx_af_audit           (audit_id),
  FULLTEXT INDEX ft_findings   (title, description),
  CONSTRAINT fk_af_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_af_audit  FOREIGN KEY (audit_id)  REFERENCES audits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- EVIDENCE REPOSITORY
-- =============================================================

CREATE TABLE IF NOT EXISTS evidence (
  id          CHAR(36)         NOT NULL DEFAULT (UUID()),
  tenant_id   CHAR(36)         NOT NULL,
  title       VARCHAR(500)     NOT NULL,
  description TEXT,
  type        VARCHAR(50)      NOT NULL DEFAULT 'document',
  file_name   VARCHAR(500),
  file_path   TEXT,
  file_size   BIGINT UNSIGNED,
  mime_type   VARCHAR(100),
  hash_sha256 VARCHAR(64),
  uploaded_by CHAR(36),
  valid_from  DATE,
  valid_until DATE,
  tags        JSON,
  created_at  DATETIME         NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  INDEX idx_evidence_tenant_valid_until (tenant_id, valid_until),
  CONSTRAINT fk_evidence_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS control_evidence (
  control_id  CHAR(36) NOT NULL,
  evidence_id CHAR(36) NOT NULL,
  tenant_id   CHAR(36) NOT NULL,
  mapped_at   DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (control_id, evidence_id),
  CONSTRAINT fk_ce_control  FOREIGN KEY (control_id)  REFERENCES controls(id) ON DELETE CASCADE,
  CONSTRAINT fk_ce_evidence FOREIGN KEY (evidence_id) REFERENCES evidence(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- POLICY MANAGER
-- =============================================================

CREATE TABLE IF NOT EXISTS policies (
  id             CHAR(36)     NOT NULL DEFAULT (UUID()),
  tenant_id      CHAR(36)     NOT NULL,
  title          VARCHAR(500) NOT NULL,
  content        LONGTEXT,
  version        VARCHAR(50)  NOT NULL DEFAULT '1.0',
  status         ENUM('draft','under_review','approved','published','retired') NOT NULL DEFAULT 'draft',
  category       VARCHAR(255),
  owner_id       CHAR(36),
  approver_id    CHAR(36),
  approved_at    DATETIME,
  effective_date DATE,
  review_date    DATE,
  created_at     DATETIME     NOT NULL DEFAULT NOW(),
  updated_at     DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  INDEX idx_policies_tenant_status (tenant_id, status),
  FULLTEXT INDEX ft_policies (title),
  CONSTRAINT fk_policies_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- VENDOR RISK
-- =============================================================

CREATE TABLE IF NOT EXISTS vendors (
  id                CHAR(36)         NOT NULL DEFAULT (UUID()),
  tenant_id         CHAR(36)         NOT NULL,
  name              VARCHAR(255)     NOT NULL,
  category          VARCHAR(100),
  criticality       ENUM('critical','high','medium','low') NOT NULL DEFAULT 'medium',
  status            ENUM('active','inactive','under_review','offboarded') NOT NULL DEFAULT 'active',
  primary_contact   VARCHAR(255),
  contact_email     VARCHAR(255),
  website           VARCHAR(500),
  services_provided TEXT,
  data_shared       JSON,
  risk_score        TINYINT UNSIGNED,
  last_assessed     DATE,
  next_assessment   DATE,
  notes             TEXT,
  created_at        DATETIME         NOT NULL DEFAULT NOW(),
  updated_at        DATETIME         NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  INDEX idx_vendors_tenant_status (tenant_id, status),
  CONSTRAINT fk_vendors_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- INCIDENT MANAGEMENT
-- =============================================================

CREATE TABLE IF NOT EXISTS incidents (
  id                               CHAR(36)     NOT NULL DEFAULT (UUID()),
  tenant_id                        CHAR(36)     NOT NULL,
  incident_id                      VARCHAR(100) NOT NULL,
  title                            VARCHAR(500) NOT NULL,
  description                      TEXT,
  type                             ENUM('data_breach','system_failure','policy_violation','fraud','physical','other') NOT NULL DEFAULT 'other',
  severity                         ENUM('critical','high','medium','low') NOT NULL DEFAULT 'medium',
  status                           ENUM('reported','investigating','contained','resolved','closed') NOT NULL DEFAULT 'reported',
  reporter_id                      CHAR(36),
  owner_id                         CHAR(36),
  detected_at                      DATETIME,
  reported_at                      DATETIME     NOT NULL DEFAULT NOW(),
  contained_at                     DATETIME,
  resolved_at                      DATETIME,
  regulatory_notification_required BOOLEAN      NOT NULL DEFAULT FALSE,
  notified_at                      DATETIME,
  root_cause                       TEXT,
  remediation_steps                TEXT,
  lessons_learned                  TEXT,
  created_at                       DATETIME     NOT NULL DEFAULT NOW(),
  updated_at                       DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  INDEX idx_incidents_tenant_status   (tenant_id, status),
  INDEX idx_incidents_tenant_severity (tenant_id, severity),
  CONSTRAINT fk_incidents_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- INTEGRATIONS
-- =============================================================

CREATE TABLE IF NOT EXISTS integrations (
  id                 CHAR(36)     NOT NULL DEFAULT (UUID()),
  tenant_id          CHAR(36)     NOT NULL,
  type               VARCHAR(50)  NOT NULL,
  name               VARCHAR(255) NOT NULL,
  credentials        TEXT         NOT NULL,
  config             JSON,
  webhook_token_hash VARCHAR(64),
  status             ENUM('active','inactive','error') NOT NULL DEFAULT 'active',
  last_sync_at       DATETIME,
  last_error         TEXT,
  created_at         DATETIME     NOT NULL DEFAULT NOW(),
  updated_at         DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_webhook_token (webhook_token_hash),
  INDEX idx_integrations_tenant (tenant_id, type),
  CONSTRAINT fk_integrations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_runs (
  id                CHAR(36)         NOT NULL DEFAULT (UUID()),
  integration_id    CHAR(36)         NOT NULL,
  tenant_id         CHAR(36)         NOT NULL,
  status            ENUM('running','completed','failed') NOT NULL DEFAULT 'running',
  records_collected INT UNSIGNED     NOT NULL DEFAULT 0,
  error_log         TEXT,
  started_at        DATETIME         NOT NULL DEFAULT NOW(),
  completed_at      DATETIME,
  PRIMARY KEY (id),
  INDEX idx_ir_integration (integration_id, started_at),
  CONSTRAINT fk_ir_integration FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- FRAMEWORK INGESTION
-- =============================================================

CREATE TABLE IF NOT EXISTS ingestion_sources (
  id             CHAR(36)     NOT NULL DEFAULT (UUID()),
  name           VARCHAR(255) NOT NULL,
  type           ENUM('json','csv','xml','api','manual') NOT NULL DEFAULT 'json',
  url            TEXT,
  schema_version VARCHAR(50),
  last_ingested  DATETIME,
  is_active      BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at     DATETIME     NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ingestion_jobs (
  id                CHAR(36)        NOT NULL DEFAULT (UUID()),
  source_id         CHAR(36),
  tenant_id         CHAR(36),
  status            ENUM('pending','running','completed','failed') NOT NULL DEFAULT 'pending',
  records_processed INT UNSIGNED    NOT NULL DEFAULT 0,
  records_failed    INT UNSIGNED    NOT NULL DEFAULT 0,
  error_message     TEXT,
  started_at        DATETIME,
  completed_at      DATETIME,
  created_at        DATETIME        NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  INDEX idx_ij_status (status),
  CONSTRAINT fk_ij_source FOREIGN KEY (source_id) REFERENCES ingestion_sources(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- SEED DATA
-- =============================================================

-- Internal superadmin tenant (never shown to real users)
INSERT IGNORE INTO tenants (
  id, name, slug, plan, status, is_active,
  billing_paid_until, settings
) VALUES (
  '00000000-0000-0000-0000-000000000001',
  'GRC Platform Admin',
  'superadmin',
  'enterprise',
  'active',
  TRUE,
  '2099-12-31 23:59:59',
  '{"theme":"dark","notifications":true}'
);

-- Tenant superadmin user
-- Password: Adm1n@GRC2024!  — CHANGE IMMEDIATELY after first login
-- Regenerate hash: node -e "require('bcrypt').hash('NEW_PW',12).then(console.log)"
INSERT IGNORE INTO users (
  id, tenant_id, email, password_hash,
  first_name, last_name, role, is_active
) VALUES (
  '00000000-0000-0000-0000-000000000002',
  '00000000-0000-0000-0000-000000000001',
  'admin@grcplatform.local',
  '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj4oU3OLG3O.',
  'Super', 'Admin',
  'superadmin',
  TRUE
);

-- System admin: NO seed row.
-- First system admin is created via the /#/sysadmin/login setup flow on first boot.
