-- ============================================================
-- DataRoom – Database Setup Script (MariaDB)
-- Run once:  mariadb -u root -p < scripts/setup-db.sql
-- ============================================================

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


GRANT ALL PRIVILEGES ON dataroom.* TO 'dataroom'@'localhost';
FLUSH PRIVILEGES;

USE dataroom;

-- ─── accounts ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS accounts (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name             VARCHAR(191) NOT NULL,
  slug             VARCHAR(100) NOT NULL,
  account_type     ENUM('individual','company') NOT NULL DEFAULT 'individual',
  created_by_email VARCHAR(255) NULL,
  brand_name       VARCHAR(191) NULL,
  brand_logo_url   VARCHAR(512) NULL,
  is_active        TINYINT(1)   NOT NULL DEFAULT 1,
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_accounts_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── permissions ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS permissions (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id           INT UNSIGNED NOT NULL,
  can_view          TINYINT(1)   NOT NULL DEFAULT 1,
  can_download      TINYINT(1)   NOT NULL DEFAULT 1,
  can_upload        TINYINT(1)   NOT NULL DEFAULT 0,
  can_create_folder TINYINT(1)   NOT NULL DEFAULT 0,
  can_delete        TINYINT(1)   NOT NULL DEFAULT 0,
  can_rename        TINYINT(1)   NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user (user_id),
  CONSTRAINT fk_perm_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── folders ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS folders (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id INT UNSIGNED NOT NULL,
  name       VARCHAR(255) NOT NULL,
  parent_id  INT UNSIGNED NULL DEFAULT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_folder_account (account_id),
  KEY idx_parent (parent_id),
  CONSTRAINT fk_folder_account FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE,
  CONSTRAINT fk_folder_parent FOREIGN KEY (parent_id)  REFERENCES folders (id) ON DELETE CASCADE,
  CONSTRAINT fk_folder_user   FOREIGN KEY (created_by) REFERENCES users   (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── files ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS files (
  id           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  account_id    INT UNSIGNED  NOT NULL,
  name         VARCHAR(255)  NOT NULL,            -- display name
  stored_name  VARCHAR(255)  NOT NULL,            -- UUID filename on disk
  folder_id    INT UNSIGNED  NULL DEFAULT NULL,
  file_size    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  mime_type    VARCHAR(255)  NULL,
  uploaded_by  INT UNSIGNED  NOT NULL,
  created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_file_account (account_id),
  KEY idx_folder   (folder_id),
  KEY idx_uploader (uploaded_by),
  FULLTEXT KEY ft_name (name),
  CONSTRAINT fk_file_account FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE,
  CONSTRAINT fk_file_folder FOREIGN KEY (folder_id)   REFERENCES folders (id) ON DELETE CASCADE,
  CONSTRAINT fk_file_user   FOREIGN KEY (uploaded_by) REFERENCES users   (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── questions ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS questions (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  username   VARCHAR(100) NOT NULL,
  title      VARCHAR(512) NOT NULL,
  body       TEXT         NOT NULL,
  status     ENUM('open','answered','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_q_account (account_id),
  KEY idx_q_user    (user_id),
  KEY idx_q_status  (status),
  KEY idx_q_created (created_at),
  CONSTRAINT fk_q_account FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE,
  CONSTRAINT fk_q_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── answers ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS answers (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id  INT UNSIGNED NOT NULL,
  question_id INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  username    VARCHAR(100) NOT NULL,
  is_admin    TINYINT(1)   NOT NULL DEFAULT 0,
  body        TEXT         NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_a_account (account_id),
  KEY idx_a_question (question_id),
  CONSTRAINT fk_a_account  FOREIGN KEY (account_id)  REFERENCES accounts  (id) ON DELETE CASCADE,
  CONSTRAINT fk_a_question FOREIGN KEY (question_id) REFERENCES questions (id) ON DELETE CASCADE,
  CONSTRAINT fk_a_user     FOREIGN KEY (user_id)     REFERENCES users     (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── audit_logs ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id     INT UNSIGNED    NULL,
  user_id       INT UNSIGNED    NULL,
  username      VARCHAR(100)    NULL,
  action        VARCHAR(64)     NOT NULL,
  resource_type VARCHAR(32)     NULL,
  resource_id   INT UNSIGNED    NULL,
  resource_name VARCHAR(512)    NULL,
  ip_address    VARCHAR(45)     NULL,    -- supports IPv6
  user_agent    TEXT            NULL,
  details       JSON            NULL,
  success       TINYINT(1)      NOT NULL DEFAULT 1,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_account (account_id),
  KEY idx_user      (user_id),
  KEY idx_action    (action),
  KEY idx_created   (created_at),
  KEY idx_username  (username),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_account FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── backward-compatible account migration helpers ─────────────────────────
INSERT INTO accounts (id, name, slug, account_type, is_active)
SELECT 1, 'Default Account', 'default', 'company', 1
WHERE NOT EXISTS (SELECT 1 FROM accounts WHERE id = 1);

ALTER TABLE accounts ADD COLUMN IF NOT EXISTS brand_name VARCHAR(191) NULL;
ALTER TABLE accounts ADD COLUMN IF NOT EXISTS brand_logo_url VARCHAR(512) NULL;
ALTER TABLE folders ADD COLUMN IF NOT EXISTS account_id INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE folders ADD INDEX IF NOT EXISTS idx_folder_account (account_id);
ALTER TABLE files ADD COLUMN IF NOT EXISTS account_id INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE files ADD INDEX IF NOT EXISTS idx_file_account (account_id);
ALTER TABLE questions ADD COLUMN IF NOT EXISTS account_id INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE questions ADD INDEX IF NOT EXISTS idx_q_account (account_id);
ALTER TABLE answers ADD COLUMN IF NOT EXISTS account_id INT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE answers ADD INDEX IF NOT EXISTS idx_a_account (account_id);
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS account_id INT UNSIGNED NULL;
ALTER TABLE audit_logs ADD INDEX IF NOT EXISTS idx_audit_account (account_id);
UPDATE folders SET account_id = 1 WHERE account_id IS NULL OR account_id = 0;
UPDATE files SET account_id = 1 WHERE account_id IS NULL OR account_id = 0;
UPDATE questions SET account_id = 1 WHERE account_id IS NULL OR account_id = 0;
UPDATE answers SET account_id = 1 WHERE account_id IS NULL OR account_id = 0;
UPDATE audit_logs SET account_id = 1 WHERE account_id IS NULL AND user_id IS NOT NULL;

UPDATE accounts SET brand_name = name WHERE brand_name IS NULL OR brand_name = '';

-- ─── plans ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS plans (
  id              INT UNSIGNED   NOT NULL AUTO_INCREMENT,
  name            VARCHAR(100)   NOT NULL,
  slug            VARCHAR(50)    NOT NULL,
  max_users       INT UNSIGNED   NOT NULL,
  max_storage_gb  DECIMAL(10,2)  NOT NULL,
  price_monthly   DECIMAL(10,2)  NOT NULL,
  is_active       TINYINT(1)     NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_plan_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO plans (name, slug, max_users, max_storage_gb, price_monthly) VALUES
  ('Starter',  'starter',   20,  5.00,  49.99),
  ('Growth',   'growth',    50, 10.00, 100.00),
  ('Business', 'business', 100, 20.00, 200.00)
ON DUPLICATE KEY UPDATE name = name;

-- ─── audit_logs retention (90 days) ─────────────────────────────────────────
-- Requires MariaDB event scheduler to be enabled on the server.
-- Example (run as privileged user): SET GLOBAL event_scheduler = ON;
CREATE EVENT IF NOT EXISTS ev_purge_audit_logs_90d
  ON SCHEDULE EVERY 1 DAY
  STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
  DO
    DELETE FROM audit_logs
    WHERE created_at < (NOW() - INTERVAL 90 DAY);

-- ─── access_logs ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS access_logs (
  id              BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  path            VARCHAR(512)      NOT NULL,
  method          VARCHAR(10)       NOT NULL DEFAULT 'GET',
  status_code     SMALLINT UNSIGNED NOT NULL DEFAULT 200,
  ip_address      VARCHAR(45)       NULL,
  user_agent      VARCHAR(512)      NULL,
  referer         VARCHAR(512)      NULL,
  accept_language VARCHAR(255)      NULL,
  account_slug    VARCHAR(100)      NULL,
  query_string    VARCHAR(512)      NULL,
  created_at      DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_acl_created (created_at),
  KEY idx_acl_ip      (ip_address),
  KEY idx_acl_path    (path(100)),
  KEY idx_acl_slug    (account_slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE EVENT IF NOT EXISTS ev_purge_access_logs_30d
  ON SCHEDULE EVERY 1 DAY
  STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
  DO
    DELETE FROM access_logs
    WHERE created_at < (NOW() - INTERVAL 30 DAY);

CREATE TABLE IF NOT EXISTS beezifi_oauth_states (
  state      VARCHAR(64)      NOT NULL,
  nonce      VARCHAR(64)      NOT NULL,
  flow       VARCHAR(10)      NOT NULL DEFAULT 'login',
  user_id    INT UNSIGNED     NULL DEFAULT NULL,
  expires_at TIMESTAMP        NOT NULL,
  created_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (state),
  KEY idx_bos_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE EVENT IF NOT EXISTS ev_purge_beezifi_states
  ON SCHEDULE EVERY 1 HOUR
  DO DELETE FROM beezifi_oauth_states WHERE expires_at < NOW();

-- ─── promo_codes ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS promo_codes (
  id                 INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  code               VARCHAR(20)      NOT NULL,
  plan_id            INT UNSIGNED     NOT NULL,
  duration_months    TINYINT UNSIGNED NOT NULL,
  created_by_user_id INT UNSIGNED     NULL,
  used_by_account_id INT UNSIGNED     NULL,
  used_at            DATETIME         NULL,
  is_active          TINYINT(1)       NOT NULL DEFAULT 1,
  created_at         DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_promo_code (code),
  KEY idx_promo_plan    (plan_id),
  KEY idx_promo_active  (is_active),
  CONSTRAINT fk_promo_plan    FOREIGN KEY (plan_id)            REFERENCES plans    (id),
  CONSTRAINT fk_promo_creator FOREIGN KEY (created_by_user_id) REFERENCES users    (id) ON DELETE SET NULL,
  CONSTRAINT fk_promo_used_by FOREIGN KEY (used_by_account_id) REFERENCES accounts (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── payment_history ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS payment_history (
  id                 BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  account_id         INT UNSIGNED     NOT NULL,
  type               ENUM('payment','promo_code') NOT NULL DEFAULT 'payment',
  plan_id            INT UNSIGNED     NOT NULL,
  plan_name          VARCHAR(100)     NOT NULL,
  duration_months    TINYINT UNSIGNED NOT NULL,
  amount             DECIMAL(10,2)    NOT NULL DEFAULT 0.00,
  promo_code_id      INT UNSIGNED     NULL,
  promo_code         VARCHAR(20)      NULL,
  stripe_intent_id   VARCHAR(255)     NULL,
  created_by_user_id INT UNSIGNED     NULL,
  created_at         DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ph_account (account_id),
  KEY idx_ph_type    (type),
  KEY idx_ph_created (created_at),
  CONSTRAINT fk_ph_account FOREIGN KEY (account_id)         REFERENCES accounts    (id) ON DELETE CASCADE,
  CONSTRAINT fk_ph_user    FOREIGN KEY (created_by_user_id) REFERENCES users       (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
