-- LegisCon MVP database foundation
-- MySQL 8 / MariaDB compatible; review database name and user in cPanel.
SET NAMES utf8mb4;
SET time_zone = '+01:00';

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('customer','researcher','editor','administrator') NOT NULL DEFAULT 'customer',
  status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
  email_verified_at DATETIME NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS jurisdictions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  level ENUM('federal','state','fct','local','other') NOT NULL,
  parent_id INT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_jurisdiction_parent FOREIGN KEY (parent_id) REFERENCES jurisdictions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legislation_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  slug VARCHAR(140) NOT NULL UNIQUE,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS government_sources (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  institution_name VARCHAR(190) NOT NULL,
  source_url VARCHAR(500) NULL,
  source_type ENUM('official_gazette','government_website','parliament','agency','court','other') NOT NULL DEFAULT 'other',
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legislation (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(300) NOT NULL,
  short_title VARCHAR(190) NULL,
  slug VARCHAR(320) NOT NULL UNIQUE,
  document_number VARCHAR(100) NULL,
  year SMALLINT UNSIGNED NULL,
  jurisdiction_id INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NULL,
  source_id BIGINT UNSIGNED NULL,
  source_url VARCHAR(500) NULL,
  summary TEXT NULL,
  document_path VARCHAR(500) NULL,
  status ENUM('draft','published','amended','repealed','archived') NOT NULL DEFAULT 'draft',
  commencement_date DATE NULL,
  enactment_date DATE NULL,
  last_verified_at DATETIME NULL,
  verified_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_legislation_year (year),
  INDEX idx_legislation_status (status),
  FULLTEXT KEY ft_legislation_search (title, short_title, summary),
  CONSTRAINT fk_legislation_jurisdiction FOREIGN KEY (jurisdiction_id) REFERENCES jurisdictions(id),
  CONSTRAINT fk_legislation_category FOREIGN KEY (category_id) REFERENCES legislation_categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_legislation_source FOREIGN KEY (source_id) REFERENCES government_sources(id) ON DELETE SET NULL,
  CONSTRAINT fk_legislation_verifier FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legislation_versions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  legislation_id BIGINT UNSIGNED NOT NULL,
  version_label VARCHAR(160) NOT NULL,
  effective_from DATE NULL,
  effective_to DATE NULL,
  source_url VARCHAR(500) NULL,
  document_path VARCHAR(500) NULL,
  verification_notes TEXT NULL,
  verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_version_legislation FOREIGN KEY (legislation_id) REFERENCES legislation(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legislation_amendments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  legislation_id BIGINT UNSIGNED NOT NULL,
  amending_title VARCHAR(300) NOT NULL,
  amendment_year SMALLINT UNSIGNED NULL,
  description TEXT NULL,
  source_url VARCHAR(500) NULL,
  effective_date DATE NULL,
  verification_status ENUM('unverified','verified','disputed') NOT NULL DEFAULT 'unverified',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_amendment_legislation FOREIGN KEY (legislation_id) REFERENCES legislation(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS questions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  reference_code VARCHAR(30) NOT NULL UNIQUE,
  subject VARCHAR(220) NOT NULL,
  question_text TEXT NOT NULL,
  jurisdiction_id INT UNSIGNED NULL,
  category_id INT UNSIGNED NULL,
  service_level ENUM('general_information','research_brief','other') NOT NULL DEFAULT 'general_information',
  status ENUM('submitted','under_review','awaiting_information','approved','answered','closed','rejected') NOT NULL DEFAULT 'submitted',
  assigned_to BIGINT UNSIGNED NULL,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  answered_at DATETIME NULL,
  CONSTRAINT fk_question_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_question_jurisdiction FOREIGN KEY (jurisdiction_id) REFERENCES jurisdictions(id) ON DELETE SET NULL,
  CONSTRAINT fk_question_category FOREIGN KEY (category_id) REFERENCES legislation_categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_question_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_question_status (status),
  INDEX idx_question_submitted (submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS question_responses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question_id BIGINT UNSIGNED NOT NULL,
  responder_id BIGINT UNSIGNED NULL,
  response_text MEDIUMTEXT NOT NULL,
  source_references JSON NULL,
  review_status ENUM('draft','pending_review','approved','returned') NOT NULL DEFAULT 'draft',
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_response_question FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
  CONSTRAINT fk_response_responder FOREIGN KEY (responder_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_response_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS articles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT UNSIGNED NULL,
  title VARCHAR(250) NOT NULL,
  slug VARCHAR(280) NOT NULL UNIQUE,
  excerpt TEXT NULL,
  body MEDIUMTEXT NOT NULL,
  status ENUM('draft','review','published','archived') NOT NULL DEFAULT 'draft',
  published_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_article_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_article_status_date (status,published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  plan_code VARCHAR(60) NOT NULL,
  status ENUM('pending','active','past_due','cancelled','expired') NOT NULL DEFAULT 'pending',
  paystack_customer_code VARCHAR(120) NULL,
  paystack_subscription_code VARCHAR(120) NULL,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_subscription_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  reference VARCHAR(120) NOT NULL UNIQUE,
  gateway VARCHAR(40) NOT NULL DEFAULT 'paystack',
  amount_kobo BIGINT UNSIGNED NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  status ENUM('initiated','pending','successful','failed','reversed') NOT NULL DEFAULT 'initiated',
  gateway_transaction_id VARCHAR(120) NULL,
  metadata JSON NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_transaction_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_transaction_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  subject VARCHAR(190) NULL,
  message TEXT NOT NULL,
  status ENUM('new','read','replied','closed') NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS newsletter_subscribers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  status ENUM('active','unsubscribed') NOT NULL DEFAULT 'active',
  consent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  details JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initial reference data. Review and extend before launch.
INSERT IGNORE INTO jurisdictions (id, name, level) VALUES
(1, 'Federal', 'federal'),
(2, 'Lagos State', 'state'),
(3, 'Ogun State', 'state'),
(4, 'Federal Capital Territory', 'fct');

INSERT IGNORE INTO legislation_categories (name, slug, description) VALUES
('Constitutional and Administrative', 'constitutional-administrative', 'Constitutional, governance and public administration matters.'),
('Business and Company', 'business-company', 'Business formation, company operations and commercial regulation.'),
('Employment and Labour', 'employment-labour', 'Employment, workplace and labour legislation.'),
('Tax and Revenue', 'tax-revenue', 'Taxation, duties, levies and public revenue.'),
('Property and Tenancy', 'property-tenancy', 'Land, property, housing and tenancy legislation.'),
('Consumer Protection', 'consumer-protection', 'Consumer rights, product and service protections.'),
('Environment and Public Health', 'environment-public-health', 'Environmental, sanitation and public health legislation.');
