#!/usr/bin/env python3
"""
Migration: create utility_providers and utility_bills tables.

Run once:  python scripts/add_utility_tables.py
Safe to re-run — uses CREATE TABLE IF NOT EXISTS.

Prefer `flask db migrate -m "add utility tables"` + `flask db upgrade` if the
Alembic chain on this server is healthy; this script is the fallback for a DB
whose schema is managed outside the chain.
"""
import sys, os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app import create_app
from app.extensions import db

app = create_app()

TABLES = [
    (
        'utility_providers',
        """
        CREATE TABLE IF NOT EXISTS utility_providers (
            id                 INT AUTO_INCREMENT PRIMARY KEY,
            name               VARCHAR(100) NOT NULL,
            utility_type       ENUM('electricity','water','gas','internet','phone','trash','other')
                               NOT NULL DEFAULT 'electricity',
            account_number     VARCHAR(100),
            usage_unit         VARCHAR(20),
            default_account_id INT,
            category_id        INT,
            billing_day        INT,
            color              VARCHAR(7)  DEFAULT '#8b5cf6',
            icon               VARCHAR(50) DEFAULT 'bi-lightning-charge',
            is_active          TINYINT(1)  DEFAULT 1,
            notes              TEXT,
            created_at         DATETIME,
            updated_at         DATETIME,
            CONSTRAINT fk_utility_provider_account  FOREIGN KEY (default_account_id) REFERENCES accounts (id),
            CONSTRAINT fk_utility_provider_category FOREIGN KEY (category_id)        REFERENCES categories (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """
    ),
    (
        'utility_bills',
        """
        CREATE TABLE IF NOT EXISTS utility_bills (
            id             INT AUTO_INCREMENT PRIMARY KEY,
            provider_id    INT NOT NULL,
            period_start   DATE NOT NULL,
            period_end     DATE NOT NULL,
            amount         DECIMAL(15,2) NOT NULL,
            due_date       DATE,
            is_paid        TINYINT(1) DEFAULT 0,
            paid_date      DATE,
            transaction_id INT,
            usage_amount   DECIMAL(15,3),
            usage_unit     VARCHAR(20),
            meter_start    DECIMAL(15,3),
            meter_end      DECIMAL(15,3),
            notes          TEXT,
            created_at     DATETIME,
            updated_at     DATETIME,
            UNIQUE KEY uq_utility_bill_period (provider_id, period_start),
            INDEX ix_utility_bills_period_start (period_start),
            INDEX ix_utility_bills_due_date     (due_date),
            INDEX ix_utility_bills_is_paid      (is_paid),
            CONSTRAINT fk_utility_bill_provider    FOREIGN KEY (provider_id)    REFERENCES utility_providers (id),
            CONSTRAINT fk_utility_bill_transaction FOREIGN KEY (transaction_id) REFERENCES transactions (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """
    ),
]

with app.app_context():
    with db.engine.connect() as conn:
        for name, sql in TABLES:
            conn.execute(db.text(sql))
            conn.commit()
            print(f'  {name} — created (or already exists).')

print('Done.')