test1

import json
import pymysql
import os
import boto3

def res(statusCode, body):
    return {
        'statusCode': statusCode,
        'body': json.dumps(body, ensure_ascii=False, default=str)
    }

def get_secret():
    client = boto3.client('secretsmanager')
    response = client.get_secret_value(
        SecretId='lambda/rds/password'
    )
    return json.loads(response['SecretString'])

def get_conn():
    secret = get_secret()
    try:
        conn = pymysql.connect(
            host = os.environ['DB_HOST'],
            port = 3306,
            user = os.environ['DB_USER'],
            password = secret['password'],
            db = os.environ['DB_NAME']
        )
        print("DB 연결 성공")
        return conn
    except Exception as e:
        print(f"DB 연결 실패: {e}")

def place_order(body):
    conn = get_conn()
    cur = conn.cursor(pymysql.cursors.DictCursor)
    order_sql = "insert into orders (customer_name) values (%s)"
    cur.execute(order_sql, (body['customer_name'],))
    order_id = cur.lastrowid
    order_items_sql = "insert into order_items (order_id, product_name, quantity, unit_price) values (%s,%s,%s,%s)"
    items = body['items']
    total_price = 0

    for item in items:
        cur.execute(order_items_sql, (order_id, item['product_name'], item['quantity'], item['unit_price']))
        total_price += item['quantity'] * item['unit_price']
    conn.commit()

    return res(200, {"result": "created", "order_id": order_id, "total_price": total_price})

def get_order(body):
    conn = get_conn()
    cur = conn.cursor(pymysql.cursors.DictCursor)
    order_sql = "select id, customer_name, status, created_at from orders where id=(%s)"
    cur.execute(order_sql, (body['order_id']))
    order_info = cur.fetchone()
    order_items_sql = "select  id, order_id,product_name, quantity, unit_price from order_items where order_id=(%s)"
    cur.execute(order_items_sql, (body['order_id']))
    order_items= cur.fetchall()
    order_info['items'] = order_items
    order_info['total_price'] = sum([item['quantity']*item['unit_price'] for item in order_items])
    return res(200, order_info)

def order_stats(body):
    conn = get_conn()
    cur = conn.cursor(pymysql.cursors.DictCursor)
    sql = "SELECT o.status, COUNT(o.id) AS count, SUM(oi_summary.total_order_revenue) AS total_revenue FROM orders o JOIN (SELECT order_id, SUM(quantity * unit_price) AS total_order_revenue FROM order_items GROUP BY order_id) oi_summary ON o.id = oi_summary.order_id GROUP BY o.status"
    cur.execute(sql)
    order_stats=cur.fetchall()
    return res(200, {"stats": order_stats})

def lambda_handler(event, context):
    body = json.loads(event['body'])
    action = body['action']
    print(action)
    if action == 'place_order':
        return place_order(body)
    elif action == 'get_order':
        return get_order(body)
    elif action == 'order_stats':
        return order_stats(body)

select region, count(*) as order_count, sum(price) total_revenue 
from orders
where status='완료'
group by region
order by order_count desc;


select c.category_name, count(o.category_id) as order_count, sum(o.price) as total_revenue, sum((o.price * (100-c.discount_rate))/100) as discounted_revenue
from orders o
join categories c
on o.category_id=c.category_id
group by c.category_name
order by discounted_revenue desc;


select o.product, c.category_name, o.price
from orders o
join categories c
on o.category_id = c.category_id
where o.price > (SELECT AVG(price) FROM orders WHERE category_id = o.category_id)
order by o.price desc;


select o.product, c.category_name, o.price, sum((o.price * (100-c.discount_rate))/100) as discounted_revenue, (sum(o.price)-sum((o.price * (100-c.discount_rate))/100)) as saved
from orders o
join categories c
on o.category_id=c.category_id
group by o.product, c.category_name, o.price
order by saved desc;


SELECT month, COUNT(order_id) AS total_orders, SUM(price) AS total_revenue, CAST(ROUND(AVG(price), 0) AS INTEGER) AS avg_price,
    COUNT(CASE WHEN status = '완료' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = '지연' THEN 1 END) AS delayed,
    COUNT(CASE WHEN status = '취소' THEN 1 END) AS cancelled
FROM orders
WHERE month = '01'
GROUP BY month;