from flask import Flask, render_template, request, jsonify, send_file
import sqlite3
import os
import json
from datetime import datetime, date

app = Flask(__name__)
DB_PATH = os.path.join(os.path.dirname(__file__), 'crm_sonuv.db')

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

def init_db():
    conn = get_db()
    conn.executescript('''
        CREATE TABLE IF NOT EXISTS khachhang (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ten TEXT NOT NULL,
            congty TEXT,
            dienthoai TEXT,
            email TEXT,
            diachi TEXT,
            nguon TEXT DEFAULT '',
            ghichu TEXT DEFAULT '',
            ngaytao TEXT DEFAULT (datetime('now','localtime')),
            ngaysua TEXT DEFAULT (datetime('now','localtime'))
        );
        CREATE TABLE IF NOT EXISTS giaodich (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            khachhang_id INTEGER NOT NULL,
            ngay TEXT NOT NULL,
            loai TEXT DEFAULT 'mua',
            sanpham TEXT DEFAULT '',
            soluong REAL DEFAULT 0,
            dongia REAL DEFAULT 0,
            thanhtien REAL DEFAULT 0,
            ghichu TEXT DEFAULT '',
            FOREIGN KEY (khachhang_id) REFERENCES khachhang(id) ON DELETE CASCADE
        );
        CREATE TABLE IF NOT EXISTS nhacnho (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            khachhang_id INTEGER,
            noidung TEXT NOT NULL,
            ngaynhac TEXT,
            trangthai TEXT DEFAULT 'chua',
            ngaytao TEXT DEFAULT (datetime('now','localtime')),
            FOREIGN KEY (khachhang_id) REFERENCES khachhang(id) ON DELETE SET NULL
        );
    ''')
    conn.commit()
    conn.close()

init_db()

@app.route('/')
def index():
    return render_template('index.html')

# === KHÁCH HÀNG ===
@app.route('/api/khachhang', methods=['GET'])
def api_khachhang():
    search = request.args.get('search', '')
    conn = get_db()
    if search:
        rows = conn.execute('''SELECT * FROM khachhang 
            WHERE ten LIKE ? OR congty LIKE ? OR dienthoai LIKE ?
            ORDER BY ngaysua DESC''', 
            (f'%{search}%', f'%{search}%', f'%{search}%')).fetchall()
    else:
        rows = conn.execute('SELECT * FROM khachhang ORDER BY ngaysua DESC').fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])

@app.route('/api/khachhang/<int:id>', methods=['GET'])
def api_khachhang_one(id):
    conn = get_db()
    row = conn.execute('SELECT * FROM khachhang WHERE id=?', (id,)).fetchone()
    conn.close()
    if not row: return jsonify({'error': 'Not found'}), 404
    return jsonify(dict(row))

@app.route('/api/khachhang', methods=['POST'])
def api_khachhang_add():
    data = request.json
    conn = get_db()
    cur = conn.execute('''
        INSERT INTO khachhang (ten, congty, dienthoai, email, diachi, nguon, ghichu)
        VALUES (?,?,?,?,?,?,?)''',
        (data['ten'], data.get('congty',''), data.get('dienthoai',''),
         data.get('email',''), data.get('diachi',''), data.get('nguon',''), data.get('ghichu','')))
    conn.commit()
    conn.close()
    return jsonify({'id': cur.lastrowid, 'message': 'OK'}), 201

@app.route('/api/khachhang/<int:id>', methods=['PUT'])
def api_khachhang_update(id):
    data = request.json
    conn = get_db()
    conn.execute('''
        UPDATE khachhang SET ten=?, congty=?, dienthoai=?, email=?, diachi=?, nguon=?, ghichu=?, 
        ngaysua=datetime('now','localtime') WHERE id=?''',
        (data['ten'], data.get('congty',''), data.get('dienthoai',''),
         data.get('email',''), data.get('diachi',''), data.get('nguon',''), data.get('ghichu',''), id))
    conn.commit()
    conn.close()
    return jsonify({'message': 'OK'})

@app.route('/api/khachhang/<int:id>', methods=['DELETE'])
def api_khachhang_delete(id):
    conn = get_db()
    conn.execute('PRAGMA foreign_keys = ON')
    conn.execute('DELETE FROM giaodich WHERE khachhang_id=?', (id,))
    conn.execute('DELETE FROM nhacnho WHERE khachhang_id=?', (id,))
    conn.execute('DELETE FROM khachhang WHERE id=?', (id,))
    conn.commit()
    conn.close()
    return jsonify({'message': 'Deleted'})

# === GIAO DỊCH ===
@app.route('/api/khachhang/<int:khid>/giaodich', methods=['GET'])
def api_giaodich(khid):
    conn = get_db()
    rows = conn.execute('SELECT * FROM giaodich WHERE khachhang_id=? ORDER BY ngay DESC', (khid,)).fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])

@app.route('/api/giaodich', methods=['POST'])
def api_giaodich_add():
    data = request.json
    conn = get_db()
    cur = conn.execute('''
        INSERT INTO giaodich (khachhang_id, ngay, loai, sanpham, soluong, dongia, thanhtien, ghichu)
        VALUES (?,?,?,?,?,?,?,?)''',
        (data['khachhang_id'], data.get('ngay', str(date.today())),
         data.get('loai','mua'), data.get('sanpham',''),
         float(data.get('soluong',0)), float(data.get('dongia',0)),
         float(data.get('thanhtien',0)), data.get('ghichu','')))
    conn.commit()
    conn.close()
    return jsonify({'id': cur.lastrowid, 'message': 'OK'}), 201

@app.route('/api/giaodich/<int:id>', methods=['DELETE'])
def api_giaodich_delete(id):
    conn = get_db()
    conn.execute('DELETE FROM giaodich WHERE id=?', (id,))
    conn.commit()
    conn.close()
    return jsonify({'message': 'Deleted'})

# === NHẮC NHỞ ===
@app.route('/api/nhacnho', methods=['GET'])
def api_nhacnho():
    conn = get_db()
    rows = conn.execute('''
        SELECT n.*, k.ten as ten_khach 
        FROM nhacnho n LEFT JOIN khachhang k ON n.khachhang_id = k.id
        ORDER BY n.trangthai, n.ngaynhac''').fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])

@app.route('/api/nhacnho', methods=['POST'])
def api_nhacnho_add():
    data = request.json
    conn = get_db()
    cur = conn.execute('''
        INSERT INTO nhacnho (khachhang_id, noidung, ngaynhac, trangthai)
        VALUES (?,?,?,?)''',
        (data.get('khachhang_id'), data['noidung'],
         data.get('ngaynhac'), data.get('trangthai','chua')))
    conn.commit()
    conn.close()
    return jsonify({'id': cur.lastrowid, 'message': 'OK'}), 201

@app.route('/api/nhacnho/<int:id>', methods=['PUT'])
def api_nhacnho_done(id):
    conn = get_db()
    conn.execute('UPDATE nhacnho SET trangthai=? WHERE id=?', ('xong', id))
    conn.commit()
    conn.close()
    return jsonify({'message': 'OK'})

@app.route('/api/nhacnho/<int:id>', methods=['DELETE'])
def api_nhacnho_delete(id):
    conn = get_db()
    conn.execute('DELETE FROM nhacnho WHERE id=?', (id,))
    conn.commit()
    conn.close()
    return jsonify({'message': 'Deleted'})

# === EXPORT / BACKUP ===
@app.route('/api/backup', methods=['GET'])
def api_backup():
    conn = get_db()
    data = {
        'khachhang': [dict(r) for r in conn.execute('SELECT * FROM khachhang').fetchall()],
        'giaodich': [dict(r) for r in conn.execute('SELECT * FROM giaodich').fetchall()],
        'nhacnho': [dict(r) for r in conn.execute('SELECT * FROM nhacnho').fetchall()],
        'ngayxuat': str(datetime.now())
    }
    conn.close()
    return jsonify(data)

@app.route('/api/restore', methods=['POST'])
def api_restore():
    data = request.json
    conn = get_db()
    conn.executescript('DELETE FROM giaodich; DELETE FROM nhacnho; DELETE FROM khachhang;')
    for kh in data.get('khachhang', []):
        conn.execute('''INSERT INTO khachhang (id,ten,congty,dienthoai,email,diachi,nguon,ghichu,ngaytao,ngaysua)
            VALUES (?,?,?,?,?,?,?,?,?,?)''',
            (kh['id'],kh['ten'],kh.get('congty',''),kh.get('dienthoai',''),
             kh.get('email',''),kh.get('diachi',''),kh.get('nguon',''),kh.get('ghichu',''),
             kh.get('ngaytao'),kh.get('ngaysua')))
    for gd in data.get('giaodich', []):
        conn.execute('''INSERT INTO giaodich (id,khachhang_id,ngay,loai,sanpham,soluong,dongia,thanhtien,ghichu)
            VALUES (?,?,?,?,?,?,?,?,?)''',
            (gd['id'],gd['khachhang_id'],gd['ngay'],gd.get('loai','mua'),
             gd.get('sanpham',''),gd.get('soluong',0),gd.get('dongia',0),
             gd.get('thanhtien',0),gd.get('ghichu','')))
    for nn in data.get('nhacnho', []):
        conn.execute('''INSERT INTO nhacnho (id,khachhang_id,noidung,ngaynhac,trangthai,ngaytao)
            VALUES (?,?,?,?,?,?)''',
            (nn['id'],nn.get('khachhang_id'),nn['noidung'],nn.get('ngaynhac'),
             nn.get('trangthai','chua'),nn.get('ngaytao')))
    conn.commit()
    conn.close()
    return jsonify({'message': 'Restored OK'})

if __name__ == '__main__':
    init_db()
    app.run(host='127.0.0.1', port=5000, debug=False)