import sqlite3, os
BASE_DIR = os.path.abspath(os.path.dirname(__file__))
DB_PATH = os.path.join(BASE_DIR, 'instance', 'data.db')
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
c.executescript('''
CREATE TABLE IF NOT EXISTS reports (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    date TEXT,
    ac_registration TEXT,
    notes TEXT,
    created_at TEXT
);
CREATE TABLE IF NOT EXISTS sectors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    report_id INTEGER,
    sector_index INTEGER,
    from_field TEXT,
    to_field TEXT,
    pax_a INTEGER,
    pax_b INTEGER,
    sector_revenue REAL,
    personal_revenue REAL,
    FOREIGN KEY(report_id) REFERENCES reports(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS images (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    report_id INTEGER,
    sector_index INTEGER,
    filename TEXT,
    FOREIGN KEY(report_id) REFERENCES reports(id) ON DELETE CASCADE
);
''')
conn.commit()
conn.close()
print('Database created at', DB_PATH)