CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    full_name VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    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;

CREATE TABLE companies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    tax_number VARCHAR(40) NULL,
    registration_number VARCHAR(40) NULL,
    address VARCHAR(255) NULL,
    city VARCHAR(100) NULL,
    country_code CHAR(2) NOT NULL DEFAULT 'MK',
    currency_code CHAR(3) NOT NULL DEFAULT 'MKD',
    fiscal_year_start_month TINYINT UNSIGNED NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_fiscal_month CHECK (fiscal_year_start_month BETWEEN 1 AND 12)
) ENGINE=InnoDB;

CREATE TABLE company_users (
    company_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    role ENUM('admin','accountant','viewer') NOT NULL DEFAULT 'viewer',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (company_id, user_id),
    CONSTRAINT fk_company_users_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_company_users_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE chart_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    account_code VARCHAR(30) NOT NULL,
    account_name VARCHAR(190) NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    account_type ENUM('asset','liability','equity','income','expense','other') NOT NULL DEFAULT 'other',
    is_analytic TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_company_account_code (company_id, account_code),
    KEY idx_accounts_parent (parent_id),
    CONSTRAINT fk_accounts_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_accounts_parent FOREIGN KEY (parent_id) REFERENCES chart_accounts(id)
) ENGINE=InnoDB;

CREATE TABLE partners (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    partner_type ENUM('customer','supplier','both') NOT NULL DEFAULT 'both',
    partner_code VARCHAR(40) NOT NULL,
    name VARCHAR(190) NOT NULL,
    tax_number VARCHAR(40) NULL,
    registration_number VARCHAR(40) NULL,
    address VARCHAR(255) NULL,
    city VARCHAR(100) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(60) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_company_partner_code (company_id, partner_code),
    KEY idx_partner_name (company_id, name),
    CONSTRAINT fk_partners_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB;

CREATE TABLE financial_vouchers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    voucher_number VARCHAR(40) NOT NULL,
    voucher_date DATE NOT NULL,
    description VARCHAR(500) NULL,
    status ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
    created_by BIGINT UNSIGNED NOT NULL,
    posted_by BIGINT UNSIGNED NULL,
    posted_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_company_voucher_number (company_id, voucher_number),
    KEY idx_voucher_date (company_id, voucher_date),
    CONSTRAINT fk_vouchers_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_vouchers_creator FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_vouchers_poster FOREIGN KEY (posted_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE voucher_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    voucher_id BIGINT UNSIGNED NOT NULL,
    line_number INT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    partner_id BIGINT UNSIGNED NULL,
    description VARCHAR(500) NULL,
    debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_voucher_line (voucher_id, line_number),
    KEY idx_line_account (account_id),
    KEY idx_line_partner (partner_id),
    CONSTRAINT fk_lines_voucher FOREIGN KEY (voucher_id) REFERENCES financial_vouchers(id),
    CONSTRAINT fk_lines_account FOREIGN KEY (account_id) REFERENCES chart_accounts(id),
    CONSTRAINT fk_lines_partner FOREIGN KEY (partner_id) REFERENCES partners(id),
    CONSTRAINT chk_line_amounts CHECK (
      debit >= 0 AND credit >= 0 AND NOT (debit > 0 AND credit > 0)
    )
) ENGINE=InnoDB;

CREATE TABLE audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id BIGINT UNSIGNED NULL,
    details JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_company_date (company_id, created_at),
    CONSTRAINT fk_audit_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;