-- =============================================================
-- GRC Platform — Full Schema
-- Run against a clean database:
--   mysql -u <user> -p <dbname> < migrations/schema.sql
-- =============================================================

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

-- =============================================================
-- 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 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_tenant_created (tenant_id, created_at),
  INDEX idx_resource (resource_type, resource_id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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_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_framework (framework_id),
  INDEX idx_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_tenant_status     (tenant_id, status),
  INDEX idx_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_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_tenant_status  (tenant_id, status),
  INDEX idx_tenant_score   (tenant_id, inherent_score),
  INDEX idx_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_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_tenant_severity (tenant_id, severity),
  INDEX idx_tenant_status   (tenant_id, status),
  INDEX idx_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_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_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_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_tenant_status   (tenant_id, status),
  INDEX idx_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;

-- =============================================================
-- 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_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;


-- =============================================================
-- DEFAULT SUPERADMIN
-- Password: Adm1n@GRC2024!  — change immediately after first login
-- Generate new hash: node -e "require('bcrypt').hash('NEW_PW',12).then(console.log)"
-- =============================================================

INSERT IGNORE INTO tenants (id, name, slug, plan, status, is_active, settings) VALUES (
  '00000000-0000-0000-0000-000000000001',
  'GRC Platform Admin',
  'superadmin',
  'enterprise',
  'active',
  TRUE,
  '{"theme":"dark","notifications":true}'
);
