84 lines
3.2 KiB
Python
84 lines
3.2 KiB
Python
#!/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.')
|