package db

const schema = `
CREATE TABLE IF NOT EXISTS companies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT,
    name TEXT NOT NULL,
    cnpj TEXT UNIQUE NOT NULL,
    cvm_code TEXT,
    sector TEXT,
    status TEXT NOT NULL DEFAULT 'ATIVO',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS filings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    external_id TEXT UNIQUE NOT NULL,
    company_id INTEGER REFERENCES companies(id),
    cnpj TEXT NOT NULL,
    category TEXT NOT NULL,
    type TEXT,
    species TEXT,
    subject TEXT,
    reference_date TEXT,
    delivery_date DATETIME NOT NULL,
    protocol TEXT,
    version TEXT,
    download_url TEXT,
    importance INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS selic_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT UNIQUE NOT NULL,
    daily_rate REAL NOT NULL,
    annual_rate REAL,
    target_rate REAL
);

CREATE TABLE IF NOT EXISTS cdi_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT UNIQUE NOT NULL,
    daily_rate REAL NOT NULL,
    annual_rate REAL
);

CREATE TABLE IF NOT EXISTS ipca_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT UNIQUE NOT NULL,
    monthly_rate REAL NOT NULL,
    accumulated_12m REAL
);

CREATE TABLE IF NOT EXISTS fx_rates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT NOT NULL,
    pair TEXT NOT NULL,
    rate REAL NOT NULL,
    UNIQUE(date, pair)
);

CREATE VIRTUAL TABLE IF NOT EXISTS companies_fts USING fts5(
    name, ticker, sector, cnpj,
    content='companies', content_rowid='id'
);

CREATE VIRTUAL TABLE IF NOT EXISTS filings_fts USING fts5(
    subject, category, type,
    content='filings', content_rowid='id'
);

CREATE TABLE IF NOT EXISTS api_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    plan TEXT NOT NULL DEFAULT 'free',
    rate_limit INTEGER NOT NULL DEFAULT 30,
    requests_today INTEGER DEFAULT 0,
    requests_month INTEGER DEFAULT 0,
    last_request_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME,
    active INTEGER DEFAULT 1
);

CREATE TABLE IF NOT EXISTS usage_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    api_key_id INTEGER REFERENCES api_keys(id),
    endpoint TEXT NOT NULL,
    method TEXT NOT NULL,
    status_code INTEGER,
    response_time_ms INTEGER,
    ip TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS usage_daily (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    api_key_id INTEGER REFERENCES api_keys(id),
    date TEXT NOT NULL,
    requests INTEGER DEFAULT 0,
    UNIQUE(api_key_id, date)
);
`