import sqlite3
from flask import g, current_app
import click

class DbController:
    def __init__(self, db_path):
        self.db_path = db_path

    def get_db(self):
        if 'db' not in g:
            g.db = sqlite3.connect(
                self.db_path,
                detect_types=sqlite3.PARSE_DECLTYPES
            )
            g.db.row_factory = sqlite3.Row
        return g.db

    def close_db(self, e=None):
        db = g.pop('db', None)
        if db is not None:
            db.close()

    def init_db(self):
        db = self.get_db()
        # Create Users Table
        db.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                email TEXT UNIQUE NOT NULL,
                password_hash TEXT NOT NULL,
                is_admin BOOLEAN DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Create Subscriptions Table
        # status can be 'active', 'canceled', 'past_due', etc.
        db.execute('''
            CREATE TABLE IF NOT EXISTS subscriptions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                stripe_subscription_id TEXT,
                stripe_customer_id TEXT,
                sub_name TEXT UNIQUE,
                unique_code TEXT UNIQUE,
                status TEXT DEFAULT 'active',
                current_period_end TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
        ''')
        db.commit()

    # --- User Helpers ---
    def get_user_by_id(self, user_id):
        db = self.get_db()
        return db.execute('SELECT * FROM users WHERE id = ?', (user_id,)).fetchone()

    def get_user_by_username(self, username):
        db = self.get_db()
        return db.execute('SELECT * FROM users WHERE username = ?', (username,)).fetchone()

    def get_user_by_email(self, email):
        db = self.get_db()
        return db.execute('SELECT * FROM users WHERE email = ?', (email,)).fetchone()

    def create_user(self, username, email, password_hash, is_admin=False):
        db = self.get_db()
        try:
            cursor = db.execute(
                'INSERT INTO users (username, email, password_hash, is_admin) VALUES (?, ?, ?, ?)',
                (username, email, password_hash, 1 if is_admin else 0)
            )
            db.commit()
            return cursor.lastrowid
        except sqlite3.IntegrityError:
            return None

    def update_password(self, user_id, new_hash):
        db = self.get_db()
        db.execute('UPDATE users SET password_hash = ? WHERE id = ?', (new_hash, user_id))
        db.commit()

    # --- Subscription Helpers ---
    def create_subscription(self, user_id, stripe_sub_id, customer_id, sub_name, unique_code, status='active'):
        db = self.get_db()
        try:
            cursor = db.execute(
                '''INSERT INTO subscriptions 
                   (user_id, stripe_subscription_id, stripe_customer_id, sub_name, unique_code, status) 
                   VALUES (?, ?, ?, ?, ?, ?)''',
                (user_id, stripe_sub_id, customer_id, sub_name, unique_code, status)
            )
            db.commit()
            return cursor.lastrowid
        except sqlite3.IntegrityError:
            return None

    def get_subscriptions_by_user(self, user_id):
        db = self.get_db()
        return db.execute('SELECT * FROM subscriptions WHERE user_id = ? ORDER BY created_at DESC', (user_id,)).fetchall()
    
    def get_subscription_by_stripe_id(self, stripe_sub_id):
        db = self.get_db()
        return db.execute('SELECT * FROM subscriptions WHERE stripe_subscription_id = ?', (stripe_sub_id,)).fetchone()

    def update_subscription_status(self, stripe_sub_id, status):
        db = self.get_db()
        db.execute('UPDATE subscriptions SET status = ? WHERE stripe_subscription_id = ?', (status, stripe_sub_id))
        db.commit()

    def check_sub_name_exists(self, name):
        db = self.get_db()
        row = db.execute('SELECT id FROM subscriptions WHERE sub_name = ?', (name,)).fetchone()
        return row is not None

    def check_code_exists(self, code):
        db = self.get_db()
        row = db.execute('SELECT id FROM subscriptions WHERE unique_code = ?', (code,)).fetchone()
        return row is not None
    
    def update_sub_name_code(self, sub_id, new_name, new_code):
        db = self.get_db()
        # This might fail if unique constraints are violated, caller should handle
        db.execute('UPDATE subscriptions SET sub_name = ?, unique_code = ? WHERE id = ?', (new_name, new_code, sub_id))
        db.commit()

    # --- Admin ---
    def get_all_users(self):
        db = self.get_db()
        return db.execute('SELECT * FROM users').fetchall()
    
    def get_all_subscriptions(self):
        db = self.get_db()
        return db.execute('''
            SELECT s.*, u.username 
            FROM subscriptions s 
            JOIN users u ON s.user_id = u.id
        ''').fetchall()


def close_db(e=None):
    db = g.pop('db', None)
    if db is not None:
        db.close()

# Helper to easily get the controller within a request
def get_db_controller():
    # In a real app we might attach this to app config, here we instantiate
    return DbController(current_app.config['DATABASE_URI'])

def init_app(app):
    app.teardown_appcontext(close_db)
    
    @app.cli.command('init-db')
    def init_db_command():
        """Clear the existing data and create new tables."""
        controller = DbController(app.config['DATABASE_URI'])
        with app.app_context():
            controller.init_db()
        click.echo('Initialized the database.')