CREATE TABLE IF NOT EXISTS tax_obligations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    frequency TEXT NOT NULL,
    taxpayer_type TEXT,
    is_active INTEGER DEFAULT 1,
    display_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tax_calendar_master (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER NOT NULL,
    obligation_code TEXT NOT NULL,
    period TEXT NOT NULL,
    period_name TEXT NOT NULL,
    digit_1 DATE,
    digit_2 DATE,
    digit_3 DATE,
    digit_4 DATE,
    digit_5 DATE,
    digit_6 DATE,
    digit_7 DATE,
    digit_8 DATE,
    digit_9 DATE,
    digit_0 DATE,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(year, obligation_code, period)
);

CREATE TABLE IF NOT EXISTS tax_calendar_status (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER NOT NULL UNIQUE,
    status TEXT NOT NULL DEFAULT 'draft',
    version INTEGER DEFAULT 1,
    source_filename TEXT,
    source_file_hash TEXT,
    total_obligations INTEGER DEFAULT 0,
    total_deadlines INTEGER DEFAULT 0,
    first_deadline DATE,
    last_deadline DATE,
    loaded_by TEXT,
    loaded_at DATETIME,
    verified_by TEXT,
    verified_at DATETIME,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tax_calendar_files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER NOT NULL,
    version INTEGER NOT NULL,
    filename TEXT NOT NULL,
    file_content BLOB,
    file_size INTEGER,
    mime_type TEXT DEFAULT 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    uploaded_by TEXT NOT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(year, version)
);

CREATE TABLE IF NOT EXISTS tax_calendar_upload_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER NOT NULL,
    filename TEXT NOT NULL,
    records_loaded INTEGER DEFAULT 0,
    records_updated INTEGER DEFAULT 0,
    records_error INTEGER DEFAULT 0,
    uploaded_by TEXT NOT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'completed',
    error_details TEXT
);

CREATE TABLE IF NOT EXISTS tax_calendar_config (
    key TEXT PRIMARY KEY,
    value TEXT,
    description TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS client_obligations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    obligation_code TEXT NOT NULL,
    is_active INTEGER DEFAULT 1,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT,
    UNIQUE(client_id, obligation_code)
);

CREATE TABLE IF NOT EXISTS client_deadline_exceptions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    year INTEGER NOT NULL,
    obligation_code TEXT NOT NULL,
    period TEXT NOT NULL,
    exception_type TEXT NOT NULL,
    custom_date DATE,
    reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT,
    UNIQUE(client_id, year, obligation_code, period)
);

CREATE TABLE IF NOT EXISTS client_custom_obligations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    code TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    frequency TEXT NOT NULL,
    year INTEGER NOT NULL,
    period TEXT NOT NULL,
    period_name TEXT,
    deadline_date DATE NOT NULL,
    is_active INTEGER DEFAULT 1,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by TEXT,
    UNIQUE(client_id, code, year, period)
);

CREATE TABLE IF NOT EXISTS tax_task_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    project_id INTEGER NOT NULL,
    obligation_code TEXT NOT NULL,
    year INTEGER NOT NULL,
    period TEXT NOT NULL,
    deadline_date DATE NOT NULL,
    task_id INTEGER,
    source TEXT DEFAULT 'master',
    generated_by TEXT NOT NULL,
    generated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(client_id, project_id, obligation_code, year, period)
);

INSERT OR IGNORE INTO tax_calendar_config (key, value, description) VALUES ('alert_days_before_year_end', '60', 'Dias antes de fin de ano para alertar');
INSERT OR IGNORE INTO tax_calendar_config (key, value, description) VALUES ('warning_days_since_load', '30', 'Dias desde ultima carga para advertencia');
INSERT OR IGNORE INTO tax_calendar_config (key, value, description) VALUES ('require_verification', '0', 'Requiere verificacion antes de usar');
INSERT OR IGNORE INTO tax_calendar_config (key, value, description) VALUES ('calendar_storage_type', 'database', 'Tipo de almacenamiento');
INSERT OR IGNORE INTO tax_calendar_config (key, value, description) VALUES ('calendar_storage_path', '', 'Ruta local de archivos');
INSERT OR IGNORE INTO tax_calendar_config (key, value, description) VALUES ('calendar_filename_pattern', 'calendario_dian_{year}.xlsx', 'Patron nombre archivo');

INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('RETEFUENTE', 'Retencion en la Fuente', 'Declaracion mensual', 'monthly', 'agente_retencion', 10);
INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('RETEIVA', 'Retencion de IVA', 'Declaracion mensual IVA', 'monthly', 'agente_retencion', 20);
INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('IVA_BIM', 'IVA Bimestral', 'Declaracion bimestral', 'bimonthly', 'responsable_iva', 30);
INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('IVA_CUATRIM', 'IVA Cuatrimestral', 'Declaracion cuatrimestral', 'quarterly', 'responsable_iva', 35);
INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('RENTA_PJ', 'Renta Personas Juridicas', 'Declaracion anual', 'annual', 'persona_juridica', 50);
INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('RENTA_GC', 'Renta Grandes Contribuyentes', 'Declaracion 3 cuotas', 'custom', 'gran_contribuyente', 55);
INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('RENTA_PN', 'Renta Personas Naturales', 'Declaracion anual', 'annual', 'persona_natural', 60);
INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('EXOGENA', 'Informacion Exogena', 'Reporte anual', 'annual', 'todos', 80);
INSERT OR IGNORE INTO tax_obligations (code, name, description, frequency, taxpayer_type, display_order) VALUES ('ICA', 'Impuesto Industria y Comercio', 'Declaracion ICA', 'custom', 'todos', 130);