CREATE TABLE IF NOT EXISTS users (
 id CHAR(36) PRIMARY KEY, email VARCHAR(190) NOT NULL UNIQUE,
 name VARCHAR(160) NOT NULL, password_hash VARCHAR(300) NOT NULL,
 role ENUM('admin','accountant','viewer') NOT NULL DEFAULT 'viewer',
 active BOOLEAN NOT NULL DEFAULT TRUE, token_version INT NOT NULL DEFAULT 0,
 created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS write_lock (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT IGNORE INTO write_lock(id) VALUES(1);

CREATE TABLE IF NOT EXISTS parties (
 id CHAR(36) PRIMARY KEY, name VARCHAR(190) NOT NULL,
 phone VARCHAR(80), notes TEXT, roles JSON NOT NULL,
 active BOOLEAN NOT NULL DEFAULT TRUE, created_by CHAR(36) NOT NULL,
 created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 FOREIGN KEY(created_by) REFERENCES users(id), INDEX(name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bank_accounts (
 id CHAR(36) PRIMARY KEY, owner_name VARCHAR(190) NOT NULL,
 owner_party_id CHAR(36), bank_name VARCHAR(160) NOT NULL,
 account_number VARCHAR(100), card_number VARCHAR(100), iban VARCHAR(100),
 notes TEXT, active BOOLEAN NOT NULL DEFAULT TRUE,
 created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 FOREIGN KEY(owner_party_id) REFERENCES parties(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS receipts (
 id CHAR(36) PRIMARY KEY, extension VARCHAR(8) NOT NULL, original_name VARCHAR(190) NOT NULL,
 mime_type VARCHAR(80) NOT NULL, size_bytes INT NOT NULL, sha256 CHAR(64) NOT NULL,
 created_by CHAR(36) NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS documents (
 id CHAR(36) PRIMARY KEY,
 type ENUM('PURCHASE','SALE','RECEIVE','PAY','ADJUSTMENT','OPENING','TRANSFER','EXPENSE','REVERSAL') NOT NULL,
 party_id CHAR(36), other_party_id CHAR(36), document_date DATE NOT NULL,
 currency ENUM('TOM','AED'), amount DECIMAL(30,6), quantity DECIMAL(30,6),
 rate DECIMAL(30,6), base_amount DECIMAL(30,6), final_amount DECIMAL(30,6),
 discount DECIMAL(30,6), party_effect DECIMAL(30,6),
 account_id CHAR(36), introduction_id CHAR(36), payer_party_id CHAR(36), payer_name VARCHAR(190),
 agent_party_id CHAR(36), agent_name VARCHAR(190), location VARCHAR(190),
 tracking_number VARCHAR(160), notes TEXT, attachment_ref CHAR(36),
 source_system VARCHAR(100), source_id VARCHAR(190),
 reversal_of CHAR(36) UNIQUE, reversed_by CHAR(36), created_by CHAR(36) NOT NULL,
 created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 UNIQUE KEY uq_source(source_system,source_id),
 INDEX idx_documents_party_date(party_id,document_date,id),
 INDEX idx_documents_date(document_date,id), INDEX(account_id,document_date),
 INDEX(payer_party_id,document_date), INDEX(tracking_number),
 FOREIGN KEY(party_id) REFERENCES parties(id),
 FOREIGN KEY(other_party_id) REFERENCES parties(id),
 FOREIGN KEY(account_id) REFERENCES bank_accounts(id),
 FOREIGN KEY(payer_party_id) REFERENCES parties(id),
 FOREIGN KEY(agent_party_id) REFERENCES parties(id),
 FOREIGN KEY(attachment_ref) REFERENCES receipts(id),
 FOREIGN KEY(reversal_of) REFERENCES documents(id),
 FOREIGN KEY(reversed_by) REFERENCES documents(id),
 FOREIGN KEY(created_by) REFERENCES users(id),
 CHECK(amount IS NULL OR amount > 0), CHECK(quantity IS NULL OR quantity > 0),
 CHECK(rate IS NULL OR rate > 0)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ledger_entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 document_id CHAR(36) NOT NULL, currency ENUM('TOM','AED') NOT NULL,
 account_code VARCHAR(100) NOT NULL, party_id CHAR(36),
 amount DECIMAL(30,6) NOT NULL,
 FOREIGN KEY(document_id) REFERENCES documents(id),
 FOREIGN KEY(party_id) REFERENCES parties(id),
 INDEX idx_ledger_party(party_id,currency,document_id),
 INDEX(document_id), CHECK(amount <> 0)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS account_introductions (
 id CHAR(36) PRIMARY KEY, party_id CHAR(36) NOT NULL,
 account_id CHAR(36) NOT NULL, trade_id CHAR(36), introduction_date DATE NOT NULL,
 announced_amount DECIMAL(30,6), direction ENUM('INCOMING','OUTGOING') NOT NULL, notes TEXT,
 created_by CHAR(36) NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 FOREIGN KEY(party_id) REFERENCES parties(id), FOREIGN KEY(account_id) REFERENCES bank_accounts(id),
 FOREIGN KEY(trade_id) REFERENCES documents(id), FOREIGN KEY(created_by) REFERENCES users(id),
 CHECK(announced_amount IS NULL OR announced_amount >= 0)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS allocations (
 id CHAR(36) PRIMARY KEY, settlement_id CHAR(36) NOT NULL, trade_id CHAR(36) NOT NULL,
 currency ENUM('TOM','AED') NOT NULL, amount DECIMAL(30,6) NOT NULL,
 voided_at TIMESTAMP(6) NULL, voided_by CHAR(36),
 created_by CHAR(36) NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 FOREIGN KEY(settlement_id) REFERENCES documents(id), FOREIGN KEY(trade_id) REFERENCES documents(id),
 FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(voided_by) REFERENCES users(id),
 INDEX(settlement_id,voided_at), INDEX(trade_id,currency,voided_at), CHECK(amount > 0)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cost_allocations (
 id CHAR(36) PRIMARY KEY, purchase_id CHAR(36) NOT NULL, sale_id CHAR(36) NOT NULL,
 quantity DECIMAL(30,6) NOT NULL,
 voided_at TIMESTAMP(6) NULL, voided_by CHAR(36),
 created_by CHAR(36) NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 FOREIGN KEY(purchase_id) REFERENCES documents(id), FOREIGN KEY(sale_id) REFERENCES documents(id),
 FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(voided_by) REFERENCES users(id),
 INDEX(purchase_id,voided_at), INDEX(sale_id,voided_at), CHECK(quantity > 0)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS nettings (
 id CHAR(36) PRIMARY KEY, first_trade_id CHAR(36) NOT NULL, second_trade_id CHAR(36) NOT NULL,
 currency ENUM('TOM','AED') NOT NULL, amount DECIMAL(30,6) NOT NULL, netting_date DATE NOT NULL,
 notes TEXT, voided_at TIMESTAMP(6) NULL, voided_by CHAR(36), created_by CHAR(36) NOT NULL,
 created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 FOREIGN KEY(first_trade_id) REFERENCES documents(id), FOREIGN KEY(second_trade_id) REFERENCES documents(id),
 FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(voided_by) REFERENCES users(id),
 CHECK(first_trade_id <> second_trade_id), CHECK(amount > 0)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS expense_links (
 document_id CHAR(36) PRIMARY KEY, sale_id CHAR(36),
 FOREIGN KEY(document_id) REFERENCES documents(id), FOREIGN KEY(sale_id) REFERENCES documents(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS idempotency_keys (
 scope_hash CHAR(64) PRIMARY KEY, request_hash CHAR(64) NOT NULL,
 result JSON NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_id CHAR(36) NOT NULL,
 action VARCHAR(100) NOT NULL, entity_type VARCHAR(80) NOT NULL, entity_id CHAR(36) NOT NULL,
 details JSON NOT NULL, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 FOREIGN KEY(actor_id) REFERENCES users(id), INDEX(entity_type,entity_id), INDEX(created_at)
) ENGINE=InnoDB;