from flask import Flask, render_template, request, redirect, url_for, flash, Response, jsonify
import sqlite3, csv, io
from datetime import date

def validate_email(email):
    if not email:
        return True
    return '@' in email and '.' in email.split('@')[-1]

def validate_phone(phone):
    if not phone:
        return True
    digits = ''.join(filter(str.isdigit, phone))
    return len(digits) == 11

app = Flask(__name__)
app.secret_key = 'stroy_key_2025'
DB_PATH = 'stroy.db'

# -------------------------------------------------------
# СОЗДАНИЕ 6 ТАБЛИЦ
# -------------------------------------------------------

def init_db():
    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    # 1. Типы материалов
    cur.execute("""CREATE TABLE IF NOT EXISTS material_types (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        name        TEXT NOT NULL,
        description TEXT
    )""")

    # 2. Материалы
    cur.execute("""CREATE TABLE IF NOT EXISTS materials (
        id               INTEGER PRIMARY KEY AUTOINCREMENT,
        name             TEXT NOT NULL,
        unit             TEXT NOT NULL DEFAULT 'шт',
        current_price    REAL NOT NULL DEFAULT 0,
        material_type_id INTEGER NOT NULL,
        FOREIGN KEY (material_type_id) REFERENCES material_types(id)
    )""")

    # 3. Контрагенты (и поставщики и партнёры в одной таблице)
    # type=1 — партнёр (расходная), type=2 — поставщик (приходная)
    cur.execute("""CREATE TABLE IF NOT EXISTS counterparties (
        id             INTEGER PRIMARY KEY AUTOINCREMENT,
        name           TEXT NOT NULL,
        contact_person TEXT,
        phone          TEXT,
        email          TEXT,
        address        TEXT,
        type           INTEGER NOT NULL DEFAULT 1
    )""")

    # 4. Заказы
    cur.execute("""CREATE TABLE IF NOT EXISTS purchase_orders (
        id               INTEGER PRIMARY KEY AUTOINCREMENT,
        number           TEXT NOT NULL,
        order_date       TEXT NOT NULL,
        delivery_date    TEXT,
        status           TEXT NOT NULL DEFAULT 'Новый',
        counterparty_id  INTEGER NOT NULL,
        FOREIGN KEY (counterparty_id) REFERENCES counterparties(id)
    )""")

    # 5. Позиции заказа
    cur.execute("""CREATE TABLE IF NOT EXISTS order_items (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id    INTEGER NOT NULL,
        material_id INTEGER NOT NULL,
        quantity    REAL NOT NULL DEFAULT 1,
        unit_price  REAL NOT NULL DEFAULT 0,
        FOREIGN KEY (order_id)    REFERENCES purchase_orders(id),
        FOREIGN KEY (material_id) REFERENCES materials(id)
    )""")

    conn.commit()

    # Начальные данные
    if cur.execute("SELECT COUNT(*) FROM material_types").fetchone()[0] == 0:
        for t in [('Кирпич и блоки','Кирпич, блоки, камень'),('Цемент и смеси','Цемент, штукатурка, шпатлёвка'),
                  ('Пиломатериалы','Доски, брус, фанера'),('Кровельные материалы','Профнастил, черепица'),
                  ('Теплоизоляция','Минвата, пенопласт'),('Отделочные материалы','Плитка, гипсокартон')]:
            cur.execute("INSERT INTO material_types (name, description) VALUES (?,?)", t)

        for m in [('Кирпич керамический М150',1,'шт',18.5),('Газобетонный блок D500',1,'шт',185.0),
                  ('Цемент ПЦ 500, 50 кг',2,'мешок',450.0),('Штукатурная смесь Ротбанд',2,'мешок',620.0),
                  ('Доска обрезная 50x150x6000',3,'шт',850.0),('Фанера ФСФ 18 мм',3,'лист',1850.0),
                  ('Профнастил НС35',4,'м²',580.0),('Черепица Braas',4,'шт',95.0),
                  ('Минвата Knauf 100 мм',5,'м²',320.0),('Пенопласт ПСБ-С 35',5,'м²',185.0),
                  ('Плитка керамическая 30x30',6,'м²',850.0),('Гипсокартон Knauf 12.5 мм',6,'лист',520.0)]:
            cur.execute("INSERT INTO materials (name, material_type_id, unit, current_price) VALUES (?,?,?,?)", m)

        # type=1 партнёры, type=2 поставщики
        for c in [('ООО СтройГрупп','Иванов И.И.','+7(495)111-22-33','info@stroygroup.ru','Москва, ул. Ленина, 10',1),
                  ('ИП Петров А.А.','Петров А.А.','+7(903)222-33-44','petrov@mail.ru','Московская обл., г. Химки',1),
                  ('АО РемСтрой','Сидоров С.С.','+7(495)333-44-55','rem@stroy.ru','Москва, пр-т Мира, 55',1),
                  ('СтройОптТорг','Козлов К.К.','+7(495)123-45-67','info@stroyopt.ru','Москва, ул. Строителей, 15',2),
                  ('КирпичМастер','Морозов М.М.','+7(499)234-56-78','sales@kirpich.ru','Подольск, пр-т Ленина, 42',2),
                  ('ЛесТорг Урал','Лесов Л.Л.','+7(343)345-67-89','les@ural.com','Екатеринбург, ул. Лесная, 8',2)]:
            cur.execute("INSERT INTO counterparties (name,contact_person,phone,email,address,type) VALUES (?,?,?,?,?,?)", c)

        # Заказы
        cur.execute("INSERT INTO purchase_orders (number,order_date,delivery_date,status,counterparty_id) VALUES ('ЗК-001','2026-01-15','2026-01-20','Выполнен',4)")
        cur.execute("INSERT INTO purchase_orders (number,order_date,delivery_date,status,counterparty_id) VALUES ('ЗК-002','2026-02-10','2026-02-15','Выполнен',1)")
        cur.execute("INSERT INTO purchase_orders (number,order_date,delivery_date,status,counterparty_id) VALUES ('ЗК-003','2026-03-01',NULL,'Новый',5)")

        # Позиции заказов
        cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (1,1,5000,18.5)")
        cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (1,3,100,450.0)")
        cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (2,5,50,850.0)")
        cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (2,9,200,320.0)")
        cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (3,7,100,580.0)")

        conn.commit()
    conn.close()

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

# -------------------------------------------------------
# ГЛАВНАЯ — материалы
# -------------------------------------------------------

@app.route('/')
def index():
    search = request.args.get('search','')
    type_filter = request.args.get('type_id','')
    conn = get_db(); cur = conn.cursor()
    sql = "SELECT m.*, mt.name AS type_name FROM materials m JOIN material_types mt ON m.material_type_id=mt.id WHERE 1=1"
    params = []
    if search: sql += " AND m.name LIKE ?"; params.append('%'+search+'%')
    if type_filter: sql += " AND m.material_type_id=?"; params.append(type_filter)
    sql += " ORDER BY m.name"
    cur.execute(sql, params)
    materials = [dict(r) for r in cur.fetchall()]
    cur.execute("SELECT * FROM material_types ORDER BY name"); types = cur.fetchall()
    stat = {
        'materials':    cur.execute("SELECT COUNT(*) FROM materials").fetchone()[0],
        'counterparties': cur.execute("SELECT COUNT(*) FROM counterparties").fetchone()[0],
        'partners':     cur.execute("SELECT COUNT(*) FROM counterparties WHERE type=1").fetchone()[0],
        'suppliers':    cur.execute("SELECT COUNT(*) FROM counterparties WHERE type=2").fetchone()[0],
        'orders':       cur.execute("SELECT COUNT(*) FROM purchase_orders").fetchone()[0],
    }
    conn.close()
    return render_template('index.html', materials=materials, types=types, search=search, type_filter=type_filter, stat=stat)

@app.route('/materials/add', methods=['GET','POST'])
def material_add():
    conn = get_db(); cur = conn.cursor()
    cur.execute("SELECT * FROM material_types ORDER BY name"); types = cur.fetchall(); conn.close()
    if request.method == 'POST':
        conn = get_db(); cur = conn.cursor()
        cur.execute("INSERT INTO materials (name,material_type_id,unit,current_price) VALUES (?,?,?,?)",
            (request.form['name'],request.form['material_type_id'],request.form['unit'],request.form['current_price']))
        conn.commit(); conn.close()
        flash('Материал добавлен','success'); return redirect(url_for('index'))
    return render_template('material_form.html', material=None, types=types, action='add')

@app.route('/materials/edit/<int:mid>', methods=['GET','POST'])
def material_edit(mid):
    conn = get_db(); cur = conn.cursor()
    cur.execute("SELECT * FROM materials WHERE id=?",(mid,)); material = cur.fetchone()
    cur.execute("SELECT * FROM material_types ORDER BY name"); types = cur.fetchall(); conn.close()
    if not material: flash('Не найдено','error'); return redirect(url_for('index'))
    if request.method == 'POST':
        conn = get_db(); cur = conn.cursor()
        cur.execute("UPDATE materials SET name=?,material_type_id=?,unit=?,current_price=? WHERE id=?",
            (request.form['name'],request.form['material_type_id'],request.form['unit'],request.form['current_price'],mid))
        conn.commit(); conn.close()
        flash('Материал обновлён','success'); return redirect(url_for('index'))
    return render_template('material_form.html', material=material, types=types, action='edit')

# -------------------------------------------------------
# КОНТРАГЕНТЫ
# -------------------------------------------------------

@app.route('/counterparties')
def counterparties_list():
    ctype = request.args.get('type','')
    conn = get_db(); cur = conn.cursor()
    sql = "SELECT c.*, COUNT(o.id) AS order_count FROM counterparties c LEFT JOIN purchase_orders o ON c.id=o.counterparty_id WHERE 1=1"
    params = []
    if ctype: sql += " AND c.type=?"; params.append(ctype)
    sql += " GROUP BY c.id ORDER BY c.name"
    cur.execute(sql, params); counterparties = cur.fetchall(); conn.close()
    return render_template('counterparties.html', counterparties=counterparties, ctype=ctype)

@app.route('/counterparties/add', methods=['GET','POST'])
def counterparty_add():
    is_ajax = request.args.get('ajax')=='1'
    default_type = request.args.get('type','1')
    if request.method == 'POST':
        name = request.form.get('name','').strip()
        phone = request.form.get('phone','').strip()
        email = request.form.get('email','').strip()
        
        # Проверки
        if not name:
            flash('Название обязательно', 'error')
        elif phone and not validate_phone(phone):
            flash('Неверный формат телефона. Должно быть 10 или 11 цифр', 'error')
        elif email and not validate_email(email):
            flash('Неверный формат email. Нужен символ @ и точка', 'error')
        else:
            conn = get_db(); cur = conn.cursor()
            cur.execute("INSERT INTO counterparties (name,contact_person,phone,email,address,type) VALUES (?,?,?,?,?,?)",
                (name,request.form.get('contact_person',''),phone,email,request.form.get('address',''),request.form.get('type',1)))
            new_id = cur.lastrowid; conn.commit(); conn.close()
            if is_ajax: return jsonify({'id':new_id,'name':name})
            flash('Контрагент добавлен','success')
            return redirect(url_for('counterparties_list'))
    
    return render_template('counterparty_form.html', counterparty=None, action='add', default_type=default_type)

@app.route('/counterparties/edit/<int:cid>', methods=['GET','POST'])
def counterparty_edit(cid):
    conn = get_db(); cur = conn.cursor()
    cur.execute("SELECT * FROM counterparties WHERE id=?",(cid,)); counterparty = cur.fetchone()
    conn.close()
    if not counterparty:
        flash('Не найдено','error')
        return redirect(url_for('counterparties_list'))
    
    if request.method == 'POST':
        name = request.form.get('name','').strip()
        phone = request.form.get('phone','').strip()
        email = request.form.get('email','').strip()
        
        # Проверки
        if not name:
            flash('Название обязательно', 'error')
        elif phone and not validate_phone(phone):
            flash('Неверный формат телефона. Должно быть ровно 11 цифр', 'error')
        elif email and not validate_email(email):
            flash('Неверный формат email. Нужен символ @ и точка', 'error')
        else:
            conn = get_db(); cur = conn.cursor()
            cur.execute("UPDATE counterparties SET name=?,contact_person=?,phone=?,email=?,address=?,type=? WHERE id=?",
                (name,request.form.get('contact_person',''),phone,email,request.form.get('address',''),request.form.get('type',1),cid))
            conn.commit(); conn.close()
            flash('Контрагент обновлён','success')
            return redirect(url_for('counterparties_list'))
    
    return render_template('counterparty_form.html', counterparty=counterparty, action='edit', default_type=counterparty['type'])

# -------------------------------------------------------
# ЗАКАЗЫ
# -------------------------------------------------------

@app.route('/orders')
def orders_list():
    sort = request.args.get('sort', 'date_desc')
    search = request.args.get('search', '').strip()
    
    conn = get_db()
    cur = conn.cursor()
    
    # Базовый SQL с поиском
    sql = """
        SELECT o.*, c.name AS counterparty_name, c.type AS counterparty_type,
               COUNT(oi.id) AS items_count
        FROM purchase_orders o
        JOIN counterparties c ON o.counterparty_id = c.id
        LEFT JOIN order_items oi ON o.id = oi.order_id
    """
    params = []
    
    if search:
        sql += " WHERE o.number LIKE ? OR c.name LIKE ? OR o.status LIKE ?"
        search_pattern = f'%{search}%'
        params = [search_pattern, search_pattern, search_pattern]
    
    sql += " GROUP BY o.id"
    
    # Сортировка
    if sort == 'date_asc':
        sql += " ORDER BY o.order_date ASC"
    elif sort == 'date_desc':
        sql += " ORDER BY o.order_date DESC"
    elif sort == 'price_asc':
        sql += " ORDER BY (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = o.id) ASC"
    elif sort == 'price_desc':
        sql += " ORDER BY (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = o.id) DESC"
    else:
        sql += " ORDER BY o.order_date DESC"
    
    cur.execute(sql, params)
    orders_raw = cur.fetchall()
    
    orders = []
    for o in orders_raw:
        # Считаем итого по позициям
        cur.execute("SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = ?", (o['id'],))
        total = cur.fetchone()[0] or 0
        orders.append({**dict(o), 'total': round(total, 2)})
    
    conn.close()
    
    return render_template('orders_list.html', orders=orders, sort=sort, search=search)


@app.route('/orders/add', methods=['GET','POST'])
def order_add():
    conn = get_db(); cur = conn.cursor()
    cur.execute("SELECT * FROM counterparties ORDER BY type, name"); counterparties = cur.fetchall()
    cur.execute("SELECT m.*, mt.name AS type_name FROM materials m JOIN material_types mt ON m.material_type_id=mt.id ORDER BY m.name")
    materials = cur.fetchall(); conn.close()
    if request.method == 'POST':
        conn = get_db(); cur = conn.cursor()
        cur.execute("INSERT INTO purchase_orders (number,order_date,delivery_date,status,counterparty_id) VALUES (?,?,?,?,?)",
            (request.form['number'],request.form['order_date'],request.form.get('delivery_date',''),
             request.form.get('status','Новый'),request.form['counterparty_id']))
        order_id = cur.lastrowid
        # Сохраняем позиции
        mat_ids   = request.form.getlist('material_id[]')
        quantities = request.form.getlist('quantity[]')
        prices     = request.form.getlist('unit_price[]')
        for mid, qty, price in zip(mat_ids, quantities, prices):
            if mid and qty and float(qty) > 0:
                cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (?,?,?,?)",
                    (order_id, mid, qty, price))
        conn.commit(); conn.close()
        flash('Заказ добавлен','success'); return redirect(url_for('orders_list'))
    today = date.today().strftime('%Y-%m-%d')
    return render_template('order_form.html', order=None, items=[], counterparties=counterparties, materials=materials, action='add', today=today)

@app.route('/orders/edit/<int:oid>', methods=['GET','POST'])
def order_edit(oid):
    conn = get_db(); cur = conn.cursor()
    cur.execute("SELECT * FROM purchase_orders WHERE id=?",(oid,)); order = cur.fetchone()
    cur.execute("SELECT oi.*, m.name AS material_name FROM order_items oi JOIN materials m ON oi.material_id=m.id WHERE oi.order_id=?",(oid,))
    items = cur.fetchall()
    cur.execute("SELECT * FROM counterparties ORDER BY type, name"); counterparties = cur.fetchall()
    cur.execute("SELECT m.*, mt.name AS type_name FROM materials m JOIN material_types mt ON m.material_type_id=mt.id ORDER BY m.name")
    materials = cur.fetchall(); conn.close()
    if not order: flash('Не найдено','error'); return redirect(url_for('orders_list'))
    if request.method == 'POST':
        conn = get_db(); cur = conn.cursor()
        cur.execute("UPDATE purchase_orders SET number=?,order_date=?,delivery_date=?,status=?,counterparty_id=? WHERE id=?",
            (request.form['number'],request.form['order_date'],request.form.get('delivery_date',''),
             request.form.get('status','Новый'),request.form['counterparty_id'],oid))
        cur.execute("DELETE FROM order_items WHERE order_id=?",(oid,))
        mat_ids   = request.form.getlist('material_id[]')
        quantities = request.form.getlist('quantity[]')
        prices     = request.form.getlist('unit_price[]')
        for mid, qty, price in zip(mat_ids, quantities, prices):
            if mid and qty and float(qty) > 0:
                cur.execute("INSERT INTO order_items (order_id,material_id,quantity,unit_price) VALUES (?,?,?,?)",
                    (oid, mid, qty, price))
        conn.commit(); conn.close()
        flash('Заказ обновлён','success'); return redirect(url_for('orders_list'))
    return render_template('order_form.html', order=order, items=items, counterparties=counterparties, materials=materials, action='edit', today=order['order_date'])

@app.route('/api/material/<int:mid>')
def api_material(mid):
    conn = get_db(); cur = conn.cursor()
    cur.execute("SELECT * FROM materials WHERE id=?",(mid,)); m = cur.fetchone(); conn.close()
    if not m: return jsonify({}), 404
    return jsonify({'price': m['current_price'], 'unit': m['unit'], 'name': m['name']})

@app.route('/export/csv')
def export_csv():
    conn = get_db(); cur = conn.cursor()
    cur.execute("SELECT m.name, mt.name AS type_name, m.unit, m.current_price FROM materials m JOIN material_types mt ON m.material_type_id=mt.id ORDER BY m.name")
    rows = cur.fetchall(); conn.close()
    output = io.StringIO(); writer = csv.writer(output)
    writer.writerow(['Наименование','Тип','Ед.','Цена'])
    for r in rows: writer.writerow([r['name'],r['type_name'],r['unit'],r['current_price']])
    output.seek(0)
    return Response('\ufeff'+output.read(), mimetype='text/csv; charset=utf-8-sig',
                    headers={'Content-Disposition':'attachment; filename=materials.csv'})

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