-- GRC Platform: Initial Schema Migration
-- Run with: mysql -u root -p grc_platform < migrations/001_initial_schema.sql

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

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

CREATE TABLE IF NOT EXISTS tenants (
  id              CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  name            VARCHAR(255) NOT NULL,
  slug            VARCHAR(100) UNIQUE NOT NULL,
  plan            ENUM('starter','growth','business','enterprise') DEFAULT 'starter',
  region          VARCHAR(100),
  industry        VARCHAR(100),
  logo_url        TEXT,
  settings        JSON,
  status          ENUM('active','suspended','trial') DEFAULT 'trial',
  trial_ends_at   DATETIME,
  created_at      DATETIME DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id              CHAR(36) PRIMARY KEY 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') DEFAULT 'viewer',
  mfa_secret      VARCHAR(100),
  mfa_enabled     BOOLEAN DEFAULT FALSE,
  failed_login_attempts TINYINT UNSIGNED DEFAULT 0,
  locked_until    DATETIME,
  last_login_at   DATETIME,
  is_active       BOOLEAN DEFAULT TRUE,
  created_at      DATETIME DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW(),
  UNIQUE KEY uq_tenant_email (tenant_id, email),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
  id              CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id         CHAR(36) NOT NULL,
  tenant_id       CHAR(36) NOT NULL,
  token_hash      VARCHAR(255) NOT NULL UNIQUE,
  ip_address      VARCHAR(45),
  user_agent      TEXT,
  expires_at      DATETIME NOT NULL,
  created_at      DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_token (token_hash),
  INDEX idx_user_tenant (user_id, tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  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 DEFAULT NOW(),
  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;

-- ============================================================
-- COMPLIANCE FRAMEWORKS & CONTROLS
-- ============================================================

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

CREATE TABLE IF NOT EXISTS framework_requirements (
  id              CHAR(36) PRIMARY KEY 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') DEFAULT 'mandatory',
  category        VARCHAR(255),
  tags            JSON,
  sort_order      INT DEFAULT 0,
  created_at      DATETIME DEFAULT NOW(),
  FOREIGN KEY (framework_id) REFERENCES regulatory_frameworks(id) ON DELETE CASCADE,
  FOREIGN KEY (parent_id) REFERENCES framework_requirements(id) ON DELETE SET NULL,
  INDEX idx_framework (framework_id),
  INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

CREATE TABLE IF NOT EXISTS controls (
  id              CHAR(36) PRIMARY KEY 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') 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') DEFAULT 'active',
  effectiveness   ENUM('effective','partially_effective','ineffective','not_tested') DEFAULT 'not_tested',
  automation_level ENUM('manual','semi_automated','automated') DEFAULT 'manual',
  last_tested_at  DATETIME,
  tags            JSON,
  created_at      DATETIME DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_tenant_status (tenant_id, status),
  INDEX idx_tenant_control_id (tenant_id, control_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS control_framework_mappings (
  id              CHAR(36) PRIMARY KEY 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') DEFAULT 'partial',
  notes           TEXT,
  mapped_at       DATETIME DEFAULT NOW(),
  UNIQUE KEY uq_control_req (control_id, requirement_id),
  FOREIGN KEY (control_id) REFERENCES controls(id) ON DELETE CASCADE,
  FOREIGN KEY (requirement_id) REFERENCES framework_requirements(id) ON DELETE CASCADE,
  INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- RISK MANAGEMENT
-- ============================================================

CREATE TABLE IF NOT EXISTS risk_categories (
  id              CHAR(36) PRIMARY KEY 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 DEFAULT NOW(),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  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) PRIMARY KEY 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') DEFAULT 'open',
  likelihood      TINYINT UNSIGNED CHECK (likelihood BETWEEN 1 AND 5),
  impact          TINYINT UNSIGNED CHECK (impact BETWEEN 1 AND 5),
  inherent_score  TINYINT UNSIGNED GENERATED ALWAYS AS (likelihood * impact) STORED,
  residual_likelihood TINYINT UNSIGNED,
  residual_impact TINYINT UNSIGNED,
  residual_score  TINYINT UNSIGNED GENERATED ALWAYS AS (residual_likelihood * residual_impact) STORED,
  target_date     DATE,
  treatment       ENUM('mitigate','accept','transfer','avoid'),
  tags            JSON,
  review_date     DATE,
  created_at      DATETIME DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES risk_categories(id) ON DELETE SET NULL,
  INDEX idx_tenant_status (tenant_id, status),
  INDEX idx_tenant_score (tenant_id, inherent_score),
  INDEX idx_tenant_risk_id (tenant_id, risk_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS risk_treatments (
  id              CHAR(36) PRIMARY KEY 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') DEFAULT 'planned',
  due_date        DATE,
  cost_estimate   DECIMAL(15,2),
  created_at      DATETIME DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (risk_id) REFERENCES risks(id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id) REFERENCES tenants(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),
  FOREIGN KEY (risk_id) REFERENCES risks(id) ON DELETE CASCADE,
  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) PRIMARY KEY 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') DEFAULT 'internal',
  framework_id    CHAR(36),
  lead_auditor_id CHAR(36),
  status          ENUM('planned','in_progress','review','completed','cancelled') 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 DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (framework_id) REFERENCES regulatory_frameworks(id) ON DELETE SET NULL,
  INDEX idx_tenant_status (tenant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_findings (
  id              CHAR(36) PRIMARY KEY 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') DEFAULT 'medium',
  status          ENUM('open','in_remediation','remediated','accepted','closed') 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 DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (audit_id) REFERENCES audits(id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_tenant_severity (tenant_id, severity),
  INDEX idx_tenant_status (tenant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- EVIDENCE & DOCUMENTS
-- ============================================================

CREATE TABLE IF NOT EXISTS evidence (
  id              CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  tenant_id       CHAR(36) NOT NULL,
  title           VARCHAR(500) NOT NULL,
  description     TEXT,
  type            ENUM('document','screenshot','log','certificate','report','policy','procedure','other'),
  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,
  is_expired      BOOLEAN GENERATED ALWAYS AS (valid_until IS NOT NULL AND valid_until < CURDATE()) STORED,
  tags            JSON,
  created_at      DATETIME DEFAULT NOW(),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_tenant_expired (tenant_id, is_expired),
  INDEX idx_tenant_valid_until (tenant_id, valid_until)
) 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 DEFAULT NOW(),
  PRIMARY KEY (control_id, evidence_id),
  FOREIGN KEY (control_id) REFERENCES controls(id) ON DELETE CASCADE,
  FOREIGN KEY (evidence_id) REFERENCES evidence(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS policies (
  id              CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  tenant_id       CHAR(36) NOT NULL,
  title           VARCHAR(500) NOT NULL,
  content         LONGTEXT,
  version         VARCHAR(50) DEFAULT '1.0',
  status          ENUM('draft','under_review','approved','published','retired') 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 DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_tenant_status (tenant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

CREATE TABLE IF NOT EXISTS incidents (
  id              CHAR(36) PRIMARY KEY 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'),
  severity        ENUM('critical','high','medium','low') DEFAULT 'medium',
  status          ENUM('reported','investigating','contained','resolved','closed') DEFAULT 'reported',
  reporter_id     CHAR(36),
  owner_id        CHAR(36),
  detected_at     DATETIME,
  reported_at     DATETIME DEFAULT NOW(),
  contained_at    DATETIME,
  resolved_at     DATETIME,
  regulatory_notification_required BOOLEAN DEFAULT FALSE,
  notified_at     DATETIME,
  root_cause      TEXT,
  lessons_learned TEXT,
  created_at      DATETIME DEFAULT NOW(),
  updated_at      DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_tenant_status (tenant_id, status),
  INDEX idx_tenant_severity (tenant_id, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- COMPLIANCE DATA INGESTION
-- ============================================================

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

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

SET FOREIGN_KEY_CHECKS = 1;
