import sqlite3

from flask import current_app, g

SCHEMA = """
CREATE TABLE company (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    name_en TEXT NOT NULL, name_ar TEXT,
    vat_no TEXT, cr_no TEXT,
    currency TEXT NOT NULL,
    address_en TEXT, address_ar TEXT, phone TEXT, email TEXT,
    lock_date TEXT,
    fy_start_month INTEGER NOT NULL DEFAULT 1,
    receivable_account_id INTEGER, payable_account_id INTEGER
);

CREATE TABLE currencies (
    code TEXT PRIMARY KEY,
    name_en TEXT NOT NULL, name_ar TEXT, symbol TEXT,
    decimals INTEGER NOT NULL DEFAULT 2,
    active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE exchange_rates (
    id INTEGER PRIMARY KEY,
    currency TEXT NOT NULL REFERENCES currencies(code),
    date TEXT NOT NULL,
    rate REAL NOT NULL,
    UNIQUE (currency, date)
);

CREATE TABLE branches (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    name_en TEXT NOT NULL, name_ar TEXT,
    currency TEXT NOT NULL REFERENCES currencies(code),
    vat_no TEXT, address_en TEXT, address_ar TEXT, phone TEXT,
    active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    full_name TEXT,
    role TEXT NOT NULL DEFAULT 'accountant',
    lang TEXT NOT NULL DEFAULT 'en',
    all_branches INTEGER NOT NULL DEFAULT 0,
    active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE user_branches (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    branch_id INTEGER NOT NULL REFERENCES branches(id),
    PRIMARY KEY (user_id, branch_id)
);

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    name_en TEXT NOT NULL, name_ar TEXT,
    kind TEXT NOT NULL,
    intercompany INTEGER NOT NULL DEFAULT 0,
    active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE journals (
    id INTEGER PRIMARY KEY,
    branch_id INTEGER NOT NULL REFERENCES branches(id),
    code TEXT NOT NULL,
    name_en TEXT NOT NULL, name_ar TEXT,
    type TEXT NOT NULL,
    default_account_id INTEGER REFERENCES accounts(id),
    active INTEGER NOT NULL DEFAULT 1,
    UNIQUE (branch_id, code)
);

CREATE TABLE taxes (
    id INTEGER PRIMARY KEY,
    name_en TEXT NOT NULL, name_ar TEXT,
    rate REAL NOT NULL,
    scope TEXT NOT NULL,
    account_id INTEGER REFERENCES accounts(id),
    active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE partners (
    id INTEGER PRIMARY KEY,
    name_en TEXT NOT NULL, name_ar TEXT,
    is_customer INTEGER NOT NULL DEFAULT 0,
    is_vendor INTEGER NOT NULL DEFAULT 0,
    vat_no TEXT, cr_no TEXT, email TEXT, phone TEXT, address TEXT,
    active INTEGER NOT NULL DEFAULT 1
);

-- Amounts are stored as integers in thousandths of the branch currency.
CREATE TABLE moves (
    id INTEGER PRIMARY KEY,
    branch_id INTEGER NOT NULL REFERENCES branches(id),
    journal_id INTEGER NOT NULL REFERENCES journals(id),
    name TEXT,
    date TEXT NOT NULL,
    ref TEXT, narration TEXT,
    state TEXT NOT NULL DEFAULT 'draft',
    move_type TEXT NOT NULL DEFAULT 'entry',
    partner_id INTEGER REFERENCES partners(id),
    transfer_ref TEXT,
    created_by INTEGER, created_at TEXT,
    posted_by INTEGER, posted_at TEXT
);
CREATE INDEX ix_moves_branch_date ON moves(branch_id, date);

CREATE TABLE move_lines (
    id INTEGER PRIMARY KEY,
    move_id INTEGER NOT NULL REFERENCES moves(id) ON DELETE CASCADE,
    branch_id INTEGER NOT NULL REFERENCES branches(id),
    account_id INTEGER NOT NULL REFERENCES accounts(id),
    partner_id INTEGER REFERENCES partners(id),
    label TEXT,
    debit INTEGER NOT NULL DEFAULT 0,
    credit INTEGER NOT NULL DEFAULT 0,
    tax_id INTEGER REFERENCES taxes(id)
);
CREATE INDEX ix_lines_account ON move_lines(account_id);
CREATE INDEX ix_lines_move ON move_lines(move_id);

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    branch_id INTEGER NOT NULL REFERENCES branches(id),
    journal_id INTEGER NOT NULL REFERENCES journals(id),
    kind TEXT NOT NULL,
    number TEXT,
    partner_id INTEGER NOT NULL REFERENCES partners(id),
    date TEXT NOT NULL, due_date TEXT,
    ref TEXT, notes TEXT,
    state TEXT NOT NULL DEFAULT 'draft',
    amount_untaxed INTEGER NOT NULL DEFAULT 0,
    amount_tax INTEGER NOT NULL DEFAULT 0,
    amount_total INTEGER NOT NULL DEFAULT 0,
    move_id INTEGER REFERENCES moves(id),
    created_by INTEGER, created_at TEXT
);

CREATE TABLE invoice_lines (
    id INTEGER PRIMARY KEY,
    invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    sequence INTEGER NOT NULL DEFAULT 0,
    description TEXT,
    account_id INTEGER NOT NULL REFERENCES accounts(id),
    quantity REAL NOT NULL DEFAULT 1,
    price_unit INTEGER NOT NULL DEFAULT 0,
    tax_id INTEGER REFERENCES taxes(id),
    subtotal INTEGER NOT NULL DEFAULT 0,
    tax_amount INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE payments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    branch_id INTEGER NOT NULL REFERENCES branches(id),
    journal_id INTEGER NOT NULL REFERENCES journals(id),
    partner_id INTEGER NOT NULL REFERENCES partners(id),
    partner_type TEXT NOT NULL,
    payment_type TEXT NOT NULL,
    date TEXT NOT NULL,
    amount INTEGER NOT NULL,
    ref TEXT,
    invoice_id INTEGER REFERENCES invoices(id),
    state TEXT NOT NULL DEFAULT 'posted',
    move_id INTEGER REFERENCES moves(id),
    created_by INTEGER, created_at TEXT
);

CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY,
    ts TEXT NOT NULL,
    user_id INTEGER,
    action TEXT NOT NULL,
    model TEXT, record_id INTEGER, details TEXT
);
"""


def connect(path):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def get_db():
    if "db" not in g:
        g.db = connect(current_app.config["DATABASE"])
    return g.db


def close_db(_exc=None):
    db = g.pop("db", None)
    if db is not None:
        db.close()


def q(sql, args=(), one=False):
    rows = get_db().execute(sql, args).fetchall()
    if one:
        return rows[0] if rows else None
    return rows


def x(sql, args=()):
    return get_db().execute(sql, args).lastrowid


def commit():
    get_db().commit()


def placeholders(seq):
    return ",".join("?" * len(seq))


def create_schema(path):
    from .migrations import migrate
    conn = connect(path)
    conn.executescript(SCHEMA)
    conn.commit()
    migrate(conn)
    conn.close()


def upgrade(path):
    from .migrations import migrate
    conn = connect(path)
    migrate(conn)
    conn.close()
