-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL CHECK(length(username) >= 3),
    password TEXT NOT NULL CHECK(length(password) >= 60),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME
);

-- Settings table
CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    association_name TEXT NOT NULL CHECK(length(association_name) > 0),
    logo_path TEXT,
    fixed_fees DECIMAL(10,2) DEFAULT 0 CHECK(fixed_fees >= 0),
    individual_entitlement_price DECIMAL(10,2) DEFAULT 0 CHECK(individual_entitlement_price >= 0),
    consumption_threshold DECIMAL(10,2) DEFAULT 0 CHECK(consumption_threshold >= 0),
    minimum_payment_amount DECIMAL(10,2) DEFAULT 0 CHECK(minimum_payment_amount >= 0),
    unit_price DECIMAL(10,2) DEFAULT 0 CHECK(unit_price >= 0),
    consumption_period TEXT DEFAULT 'monthly' CHECK(consumption_period IN ('monthly', 'bimonthly', 'quarterly')),
    penalty_rate DECIMAL(10,2) DEFAULT 0 CHECK(penalty_rate >= 0),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Subscribers table
CREATE TABLE IF NOT EXISTS subscribers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    meter_number TEXT UNIQUE NOT NULL CHECK(length(meter_number) > 0),
    full_name TEXT NOT NULL CHECK(length(full_name) > 0),
    national_id TEXT NOT NULL CHECK(length(national_id) > 0),
    address TEXT,
    phone TEXT,
    email TEXT,
    family_members INTEGER NOT NULL DEFAULT 1 CHECK(family_members >= 0),
    status INTEGER DEFAULT 1 CHECK(status IN (0, 1)),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_subscribers_meter ON subscribers(meter_number);
CREATE INDEX IF NOT EXISTS idx_subscribers_national_id ON subscribers(national_id);
CREATE INDEX IF NOT EXISTS idx_subscribers_status ON subscribers(status);

-- Consumption tiers table
CREATE TABLE IF NOT EXISTS consumption_tiers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    start_range DECIMAL(10,2) NOT NULL CHECK(start_range >= 0),
    end_range DECIMAL(10,2) CHECK(end_range > start_range OR end_range IS NULL),
    price_per_unit DECIMAL(10,2) NOT NULL CHECK(price_per_unit > 0),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_tiers_range ON consumption_tiers(start_range, end_range);

-- Water consumption table
CREATE TABLE IF NOT EXISTS consumption (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subscriber_id INTEGER NOT NULL,
    previous_reading DECIMAL(10,2) NOT NULL CHECK(previous_reading >= 0),
    current_reading DECIMAL(10,2) NOT NULL CHECK(current_reading >= previous_reading),
    reading_date DATE NOT NULL,
    consumption_period TEXT NOT NULL CHECK(consumption_period IN ('monthly', 'bimonthly', 'quarterly')),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_consumption_subscriber ON consumption(subscriber_id);
CREATE INDEX IF NOT EXISTS idx_consumption_date ON consumption(reading_date);

-- Invoices table
CREATE TABLE IF NOT EXISTS invoices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subscriber_id INTEGER NOT NULL,
    consumption_id INTEGER NOT NULL UNIQUE,
    fixed_fees DECIMAL(10,2) NOT NULL CHECK(fixed_fees >= 0),
    collective_entitlement_amount DECIMAL(10,2) NOT NULL CHECK(collective_entitlement_amount >= 0),
    consumption_amount DECIMAL(10,2) NOT NULL CHECK(consumption_amount >= 0),
    penalties DECIMAL(10,2) DEFAULT 0 CHECK(penalties >= 0),
    total_amount DECIMAL(10,2) NOT NULL CHECK(total_amount = fixed_fees + collective_entitlement_amount + consumption_amount + penalties),
    status TEXT CHECK(status IN ('paid', 'unpaid', 'cancelled')) NOT NULL DEFAULT 'unpaid',
    due_date DATE NOT NULL,
    payment_date DATE,
    payment_method TEXT CHECK(payment_method IN ('cash', 'check', 'transfer') OR payment_method IS NULL),
    invoice_number TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE RESTRICT,
    FOREIGN KEY (consumption_id) REFERENCES consumption(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_invoices_subscriber ON invoices(subscriber_id);
CREATE INDEX IF NOT EXISTS idx_invoices_status ON invoices(status);
CREATE INDEX IF NOT EXISTS idx_invoices_due_date ON invoices(due_date);

-- Activity log table
CREATE TABLE IF NOT EXISTS activity_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    action TEXT NOT NULL,
    details TEXT,
    ip_address TEXT,
    user_agent TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Payments table
CREATE TABLE IF NOT EXISTS payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_id INTEGER NOT NULL,
    amount DECIMAL(10,2) NOT NULL CHECK(amount > 0),
    payment_date DATE NOT NULL,
    payment_method TEXT CHECK(payment_method IN ('cash', 'check', 'transfer', 'invoice')),
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_payments_invoice ON payments(invoice_id);
CREATE INDEX IF NOT EXISTS idx_payments_date ON payments(payment_date);

CREATE INDEX IF NOT EXISTS idx_activity_user ON activity_log(user_id);
CREATE INDEX IF NOT EXISTS idx_activity_date ON activity_log(created_at);

-- Trigger to update timestamps
CREATE TRIGGER IF NOT EXISTS update_settings_timestamp 
   AFTER UPDATE ON settings
BEGIN
   UPDATE settings SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_subscribers_timestamp
   AFTER UPDATE ON subscribers
BEGIN
   UPDATE subscribers SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_consumption_tiers_timestamp
   AFTER UPDATE ON consumption_tiers
BEGIN
   UPDATE consumption_tiers SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_consumption_timestamp
   AFTER UPDATE ON consumption
BEGIN
   UPDATE consumption SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_invoices_timestamp
   AFTER UPDATE ON invoices
BEGIN
   UPDATE invoices SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Views for common queries
CREATE VIEW IF NOT EXISTS view_unpaid_invoices AS
SELECT 
    i.id as invoice_id,
    s.meter_number,
    s.full_name,
    i.total_amount,
    i.due_date,
    julianday('now') - julianday(i.due_date) as days_overdue
FROM invoices i
JOIN subscribers s ON i.subscriber_id = s.id
WHERE i.status = 'unpaid'
ORDER BY i.due_date;

CREATE VIEW IF NOT EXISTS view_consumption_summary AS
SELECT 
    s.meter_number,
    s.full_name,
    c.reading_date,
    c.previous_reading,
    c.current_reading,
    (c.current_reading - c.previous_reading) as consumption,
    i.total_amount,
    i.status as payment_status
FROM consumption c
JOIN subscribers s ON c.subscriber_id = s.id
LEFT JOIN invoices i ON c.id = i.consumption_id
ORDER BY c.reading_date DESC;
