-- ============================================================================
-- Kubera Wealth Management — Database Schema (MariaDB 10.6+)
-- Phase 1: public marketing site (portfolio, insights, team, careers, events,
-- contact, newsletter, applications). Auth/portal tables are stubbed for
-- Phase 2 (admin, investors, founders) but not wired up to the app yet.
-- ============================================================================

CREATE DATABASE IF NOT EXISTS kuberawm
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE kuberawm;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- categories — shared taxonomy for portfolio companies & insights articles
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS categories (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(100)    NOT NULL,
  slug          VARCHAR(120)    NOT NULL UNIQUE,
  type          ENUM('sector','article') NOT NULL DEFAULT 'sector',
  icon          VARCHAR(60)     DEFAULT NULL COMMENT 'Lucide icon name',
  description   VARCHAR(255)    DEFAULT NULL,
  sort_order    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_categories_type (type),
  INDEX idx_categories_slug (slug)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- funds — investment vehicles (used for stats & portfolio attribution)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS funds (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(120)  NOT NULL,
  vintage_year   YEAR          DEFAULT NULL,
  size_usd       BIGINT UNSIGNED DEFAULT NULL,
  status         ENUM('active','fully_deployed','closed') NOT NULL DEFAULT 'active',
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- companies — portfolio companies
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS companies (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name              VARCHAR(150)    NOT NULL,
  slug              VARCHAR(170)    NOT NULL UNIQUE,
  logo_path         VARCHAR(255)    DEFAULT NULL,
  cover_image_path  VARCHAR(255)    DEFAULT NULL,
  tagline           VARCHAR(255)    DEFAULT NULL,
  description        TEXT            DEFAULT NULL,
  category_id       INT UNSIGNED    DEFAULT NULL,
  fund_id           INT UNSIGNED    DEFAULT NULL,
  stage             ENUM('pre-seed','seed','series-a','series-b','series-c','growth','ipo','acquired') NOT NULL DEFAULT 'seed',
  status            ENUM('active','exited','acquired','shut_down') NOT NULL DEFAULT 'active',
  is_unicorn        TINYINT(1)      NOT NULL DEFAULT 0,
  valuation_usd     BIGINT UNSIGNED DEFAULT NULL,
  founded_year      YEAR            DEFAULT NULL,
  investment_date   DATE            DEFAULT NULL,
  headquarters      VARCHAR(120)    DEFAULT NULL,
  country           VARCHAR(80)     DEFAULT NULL,
  latitude          DECIMAL(10,6)   DEFAULT NULL,
  longitude         DECIMAL(10,6)   DEFAULT NULL,
  website_url       VARCHAR(255)    DEFAULT NULL,
  is_featured       TINYINT(1)      NOT NULL DEFAULT 0,
  sort_order        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  meta_title        VARCHAR(160)    DEFAULT NULL,
  meta_description  VARCHAR(255)    DEFAULT NULL,
  created_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_companies_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_companies_fund     FOREIGN KEY (fund_id)     REFERENCES funds(id)     ON DELETE SET NULL,
  INDEX idx_companies_stage (stage),
  INDEX idx_companies_status (status),
  INDEX idx_companies_featured (is_featured),
  INDEX idx_companies_category (category_id),
  FULLTEXT INDEX ftx_companies_search (name, tagline, description)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- team — team member profiles
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS team (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(120)  NOT NULL,
  slug           VARCHAR(140)  NOT NULL UNIQUE,
  title          VARCHAR(150)  NOT NULL,
  photo_path     VARCHAR(255)  DEFAULT NULL,
  bio            TEXT          DEFAULT NULL,
  focus_areas    VARCHAR(255)  DEFAULT NULL COMMENT 'Comma separated sector focus',
  linkedin_url   VARCHAR(255)  DEFAULT NULL,
  twitter_url    VARCHAR(255)  DEFAULT NULL,
  email          VARCHAR(150)  DEFAULT NULL,
  department     ENUM('leadership','investment','platform','operations') NOT NULL DEFAULT 'investment',
  sort_order     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  is_active      TINYINT(1)    NOT NULL DEFAULT 1,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- articles — insights / blog
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS articles (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title             VARCHAR(220)  NOT NULL,
  slug              VARCHAR(240)  NOT NULL UNIQUE,
  excerpt           VARCHAR(400)  DEFAULT NULL,
  body_markdown     LONGTEXT      NOT NULL,
  cover_image_path  VARCHAR(255)  DEFAULT NULL,
  author_id         INT UNSIGNED  DEFAULT NULL,
  category_id       INT UNSIGNED  DEFAULT NULL,
  tags              VARCHAR(255)  DEFAULT NULL COMMENT 'Comma separated tags',
  is_featured       TINYINT(1)    NOT NULL DEFAULT 0,
  reading_minutes   SMALLINT UNSIGNED DEFAULT NULL,
  status            ENUM('draft','published') NOT NULL DEFAULT 'published',
  published_at      DATETIME      DEFAULT NULL,
  meta_title        VARCHAR(160)  DEFAULT NULL,
  meta_description  VARCHAR(255)  DEFAULT NULL,
  view_count        INT UNSIGNED  NOT NULL DEFAULT 0,
  created_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_articles_author   FOREIGN KEY (author_id)   REFERENCES team(id)       ON DELETE SET NULL,
  CONSTRAINT fk_articles_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  INDEX idx_articles_status_published (status, published_at),
  INDEX idx_articles_featured (is_featured),
  FULLTEXT INDEX ftx_articles_search (title, excerpt, body_markdown)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- jobs — careers / open positions
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS jobs (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(150)  NOT NULL,
  slug           VARCHAR(170)  NOT NULL UNIQUE,
  department     VARCHAR(100)  NOT NULL,
  location       VARCHAR(120)  NOT NULL,
  employment_type ENUM('full-time','part-time','contract','internship') NOT NULL DEFAULT 'full-time',
  description    TEXT          NOT NULL,
  requirements   TEXT          DEFAULT NULL,
  is_remote      TINYINT(1)    NOT NULL DEFAULT 0,
  status         ENUM('open','closed') NOT NULL DEFAULT 'open',
  posted_at      DATE          DEFAULT NULL,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_jobs_status (status)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- job_applications — resumes submitted against a job posting
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS job_applications (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  job_id         INT UNSIGNED  NOT NULL,
  full_name      VARCHAR(150)  NOT NULL,
  email          VARCHAR(150)  NOT NULL,
  phone          VARCHAR(40)   DEFAULT NULL,
  linkedin_url   VARCHAR(255)  DEFAULT NULL,
  resume_path    VARCHAR(255)  NOT NULL,
  cover_note     TEXT          DEFAULT NULL,
  status         ENUM('new','reviewing','interviewing','rejected','hired') NOT NULL DEFAULT 'new',
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_applications_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  INDEX idx_job_applications_status (status)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- founder_applications — "Apply for Funding" submissions
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS founder_applications (
  id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_name       VARCHAR(150)  NOT NULL,
  founder_name       VARCHAR(150)  NOT NULL,
  email              VARCHAR(150)  NOT NULL,
  phone              VARCHAR(40)   DEFAULT NULL,
  website_url        VARCHAR(255)  DEFAULT NULL,
  sector             VARCHAR(100)  DEFAULT NULL,
  stage              VARCHAR(60)   DEFAULT NULL,
  raise_amount_usd   BIGINT UNSIGNED DEFAULT NULL,
  summary            TEXT          NOT NULL,
  pitch_deck_path    VARCHAR(255)  DEFAULT NULL,
  financials_path    VARCHAR(255)  DEFAULT NULL,
  business_plan_path VARCHAR(255)  DEFAULT NULL,
  status             ENUM('new','reviewing','meeting_scheduled','passed','invested') NOT NULL DEFAULT 'new',
  created_at         TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_founder_applications_status (status)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- events
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS events (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(180)  NOT NULL,
  slug           VARCHAR(200)  NOT NULL UNIQUE,
  description    TEXT          DEFAULT NULL,
  cover_image_path VARCHAR(255) DEFAULT NULL,
  location       VARCHAR(180)  DEFAULT NULL,
  is_virtual     TINYINT(1)    NOT NULL DEFAULT 0,
  starts_at      DATETIME      NOT NULL,
  ends_at        DATETIME      DEFAULT NULL,
  capacity       INT UNSIGNED  DEFAULT NULL,
  status         ENUM('upcoming','past','cancelled') NOT NULL DEFAULT 'upcoming',
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_events_starts_at (starts_at),
  INDEX idx_events_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS event_registrations (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id     INT UNSIGNED  NOT NULL,
  full_name    VARCHAR(150)  NOT NULL,
  email        VARCHAR(150)  NOT NULL,
  company      VARCHAR(150)  DEFAULT NULL,
  created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_registrations_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  UNIQUE KEY uq_event_email (event_id, email)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- testimonials
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS testimonials (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote          TEXT          NOT NULL,
  author_name    VARCHAR(150)  NOT NULL,
  author_title   VARCHAR(150)  DEFAULT NULL,
  company_id     INT UNSIGNED  DEFAULT NULL,
  photo_path     VARCHAR(255)  DEFAULT NULL,
  sort_order     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  is_active      TINYINT(1)    NOT NULL DEFAULT 1,
  CONSTRAINT fk_testimonials_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- site_stats — animated homepage metrics ($5B+ AUM, 150+ companies, etc.)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS site_stats (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label          VARCHAR(100)  NOT NULL,
  value_numeric  BIGINT        NOT NULL,
  value_prefix   VARCHAR(10)   DEFAULT NULL,
  value_suffix   VARCHAR(10)   DEFAULT NULL,
  sort_order     SMALLINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- contacts — general contact form submissions
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contacts (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(150)  NOT NULL,
  email          VARCHAR(150)  NOT NULL,
  subject        VARCHAR(200)  DEFAULT NULL,
  message        TEXT          NOT NULL,
  ip_address     VARCHAR(45)   DEFAULT NULL,
  status         ENUM('new','read','replied','archived') NOT NULL DEFAULT 'new',
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- newsletter_subscribers
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email          VARCHAR(150)  NOT NULL UNIQUE,
  status         ENUM('active','unsubscribed') NOT NULL DEFAULT 'active',
  source         VARCHAR(60)   DEFAULT NULL,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- Phase 2 stubs (not yet used by the app): admin/investor auth, CMS, logging.
-- Created now so the schema is forward-compatible without later migrations
-- breaking foreign keys.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admins (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(120)  NOT NULL,
  email          VARCHAR(150)  NOT NULL UNIQUE,
  password_hash  VARCHAR(255)  NOT NULL,
  role           ENUM('super_admin','editor','viewer') NOT NULL DEFAULT 'editor',
  is_active      TINYINT(1)    NOT NULL DEFAULT 1,
  last_login_at  DATETIME      DEFAULT NULL,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS investors (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(150)  NOT NULL,
  email          VARCHAR(150)  NOT NULL UNIQUE,
  password_hash  VARCHAR(255)  NOT NULL,
  fund_id        INT UNSIGNED  DEFAULT NULL,
  commitment_usd BIGINT UNSIGNED DEFAULT NULL,
  is_active      TINYINT(1)    NOT NULL DEFAULT 1,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_investors_fund FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS logs (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type     ENUM('admin','investor','system') NOT NULL DEFAULT 'system',
  actor_id       INT UNSIGNED  DEFAULT NULL,
  action         VARCHAR(150)  NOT NULL,
  entity         VARCHAR(80)   DEFAULT NULL,
  entity_id      INT UNSIGNED  DEFAULT NULL,
  ip_address     VARCHAR(45)   DEFAULT NULL,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_logs_actor (actor_type, actor_id),
  INDEX idx_logs_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settings (
  `key`          VARCHAR(100)  NOT NULL PRIMARY KEY,
  `value`        TEXT          DEFAULT NULL,
  updated_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;
