CREATE TABLE IF NOT EXISTS tickets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    company_id INTEGER NOT NULL DEFAULT 1,
    device_id INTEGER NULL,
    ticket_id TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL,
    brief TEXT,
    body TEXT NOT NULL,
    opened_at_ms INTEGER NOT NULL,
    created_at_ms INTEGER NOT NULL,
    updated_at_ms INTEGER NOT NULL,
    owner_username TEXT NOT NULL DEFAULT '',
    verified_by_username TEXT,
    device_model TEXT,
    app_package TEXT,
    app_version TEXT,
    os_name TEXT,
    os_version TEXT,
    rating INTEGER NOT NULL DEFAULT 0,
    tags_json TEXT NOT NULL DEFAULT '[]',
    payload_json TEXT NOT NULL,
    FOREIGN KEY (company_id) REFERENCES companies(id),
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_tickets_company ON tickets(company_id);
CREATE INDEX IF NOT EXISTS idx_tickets_opened ON tickets(opened_at_ms DESC);