-- Accounting Phase 1 — Core Accounting Schema
-- Run this file against an empty MariaDB database.
-- Compatible: MariaDB 10.6+

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

-- ─── Tenants ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tenants (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  plan        ENUM('starter','professional','enterprise') DEFAULT 'starter',
  created_at  DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Users ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(255) NOT NULL,
  email         VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('admin','accountant') DEFAULT 'accountant',
  created_at    DATETIME DEFAULT NOW(),
  updated_at    DATETIME ON UPDATE NOW(),
  UNIQUE KEY uq_email (email),
  INDEX idx_tenant (tenant_id),
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Accounts (Chart of Accounts) ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS accounts (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  code       VARCHAR(20) NOT NULL,
  name       VARCHAR(255) NOT NULL,
  type       ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  parent_id  INT UNSIGNED DEFAULT NULL,
  is_deleted TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT NOW(),
  updated_at DATETIME ON UPDATE NOW(),
  UNIQUE KEY uq_code_per_tenant (tenant_id, code),
  INDEX idx_tenant (tenant_id),
  INDEX idx_parent (parent_id),
  INDEX idx_type (tenant_id, type),
  CONSTRAINT fk_accounts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_accounts_parent FOREIGN KEY (parent_id) REFERENCES accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Journal Entries ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS journal_entries (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NOT NULL,
  date       DATE NOT NULL,
  memo       TEXT DEFAULT NULL,
  created_by INT UNSIGNED NOT NULL,
  is_deleted TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT NOW(),
  updated_at DATETIME ON UPDATE NOW(),
  INDEX idx_tenant_date (tenant_id, date),
  INDEX idx_tenant (tenant_id),
  CONSTRAINT fk_je_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_je_user   FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Journal Lines ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS journal_lines (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entry_id    INT UNSIGNED NOT NULL,
  account_id  INT UNSIGNED NOT NULL,
  debit       DECIMAL(15,2) DEFAULT 0.00,
  credit      DECIMAL(15,2) DEFAULT 0.00,
  description VARCHAR(500) DEFAULT NULL,
  created_at  DATETIME DEFAULT NOW(),
  INDEX idx_entry (entry_id),
  INDEX idx_account (account_id),
  CONSTRAINT fk_jl_entry   FOREIGN KEY (entry_id)   REFERENCES journal_entries(id) ON DELETE CASCADE,
  CONSTRAINT fk_jl_account FOREIGN KEY (account_id) REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
