"""Build sql/install.sql from the trusted phpMyAdmin dump."""
from __future__ import annotations

from pathlib import Path

SRC = Path(r"C:\Users\redmi\Downloads\charlies.barber-shop.sql")
DEST = Path(__file__).resolve().parents[1] / "sql" / "install.sql"

OWNER_HASH = "$2y$10$uNBtWWIebUNFgZOwZulaT.QqwPgb8sH8zHMsgflXORWWdqD0fRdh."
CUSTOMER_HASH = "$2y$10$QIz5icexJAMOrw5tQP8imuuZKtqmAZlQqUuM2UoFmZgO2hbCs/wJu"

HEADER = """-- BarberShop hosted shop installer
-- Source: C:\\\\Users\\\\redmi\\\\Downloads\\\\charlies.barber-shop.sql
--         (phpMyAdmin dump 2026-08-23, schema + catalog from live demo)
--
-- Production test tenant: charlies.barber-shop.us
-- Branding in this file is generic on purpose. Owner edits company name
-- in Admin → Page Content / Branding.
--
--   tenants.subdomain = charlies   (hostname routing)
--   tenants.name / business_name   = Company Name Here
--   hero_headline                  = Title Here
--
-- Roles:
--   owner  = shop admin (web /admin)
--   customer = public users (web portal + Android app)
--
-- Test logins (change after go-live):
--   Owner:    admin / password32
--   Customer: sam@charlies.barber-shop.us / customer32
--
-- In cPanel phpMyAdmin: select the shop database first, then Import this file.
-- Do not wrap this in CREATE DATABASE on GoDaddy (the DB already exists).
-- Personal demo-client rows from the dump are not copied.

"""

DROPS = """
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS demo_presence;
DROP TABLE IF EXISTS shop_order_items;
DROP TABLE IF EXISTS shop_orders;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS booking_reminders;
DROP TABLE IF EXISTS staff_services;
DROP TABLE IF EXISTS staff_time_off;
DROP TABLE IF EXISTS style_gallery;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS services;
DROP TABLE IF EXISTS staff_barbers;
DROP TABLE IF EXISTS media_assets;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS reminder_settings;
DROP TABLE IF EXISTS client_policy;
DROP TABLE IF EXISTS payment_settings;
DROP TABLE IF EXISTS page_settings;
DROP TABLE IF EXISTS license_metrics;
DROP TABLE IF EXISTS sms_carriers;
DROP TABLE IF EXISTS tenants;
DROP TABLE IF EXISTS schema_migrations;
SET FOREIGN_KEY_CHECKS = 1;

"""

USERS_INSERT = f"""INSERT INTO `users` (`id`, `tenant_id`, `name`, `phone`, `email`, `username`, `role`, `carrier_id`, `custom_sms_domain`, `password_hash`, `login_token`, `login_token_expires`, `sms_opt_in`, `email_opt_in`, `notes`, `created_at`, `updated_at`) VALUES
(1, 1, 'Shop Owner', '5550142218', 'owner@charlies.barber-shop.us', 'admin', 'owner', NULL, NULL, '{OWNER_HASH}', NULL, NULL, 0, 1, 'Shop owner / admin', '2026-08-25 00:00:00', '2026-08-25 00:00:00'),
(2, 1, 'Sam Rivera', '5550101888', 'sam@charlies.barber-shop.us', NULL, 'customer', NULL, NULL, '{CUSTOMER_HASH}', NULL, NULL, 1, 1, 'Test customer for the Android app', '2026-08-25 00:00:00', '2026-08-25 00:00:00');
"""

BOOKINGS_INSERT = """INSERT INTO `bookings` (`id`, `tenant_id`, `service_id`, `staff_id`, `user_id`, `customer_name`, `customer_phone`, `customer_email`, `starts_at`, `ends_at`, `status`, `notes`, `payment_status`, `deposit_paid`, `created_at`) VALUES
(1, 1, 2, 1, 2, 'Sam Rivera', '5550101888', 'sam@charlies.barber-shop.us', DATE_ADD(CURDATE(), INTERVAL 1 DAY) + INTERVAL 11 HOUR, DATE_ADD(CURDATE(), INTERVAL 1 DAY) + INTERVAL 11 HOUR + INTERVAL 50 MINUTE, 'confirmed', 'App test booking', 'unpaid', 0.00, CURRENT_TIMESTAMP);
"""


def replace_insert_block(sql: str, table: str, replacement: str) -> str:
    needle = f"INSERT INTO `{table}`"
    start = sql.index(needle)
    end = sql.index(";\n", start) + 2
    return sql[:start] + replacement.rstrip() + "\n" + sql[end:]


def main() -> None:
    sql = SRC.read_text(encoding="utf-8")

    # Drop phpMyAdmin DB comment that names demobarbershop
    sql = sql.replace("-- Database: `demobarbershop`", "-- Database: (select the shop DB in phpMyAdmin first)")

    sql = sql.replace(
        "  `email` varchar(150) DEFAULT NULL,\n"
        "  `carrier_id` int(11) DEFAULT NULL,",
        "  `email` varchar(150) DEFAULT NULL,\n"
        "  `username` varchar(80) DEFAULT NULL,\n"
        "  `role` varchar(20) NOT NULL DEFAULT 'customer',\n"
        "  `carrier_id` int(11) DEFAULT NULL,",
    )

    sql = sql.replace(
        "  `email_from_name` varchar(150) DEFAULT 'Classic Cuts',",
        "  `email_from_name` varchar(150) DEFAULT 'Company Name Here',",
    )

    sql = replace_insert_block(
        sql,
        "tenants",
        "INSERT INTO `tenants` (`id`, `name`, `subdomain`, `custom_domain`, `primary_color`, `secondary_color`, `font_pairing`, `license_status`, `created_at`) VALUES\n"
        "(1, 'Company Name Here', 'charlies', NULL, '#1c1410', '#d4a017', 'classic_serif', 'active', '2026-08-25 00:00:00');\n",
    )

    sql = replace_insert_block(
        sql,
        "page_settings",
        "INSERT INTO `page_settings` (`tenant_id`, `hero_headline`, `hero_subheadline`, `hero_media_path`, `cta_text`, `cta_link`, `business_name`, `address`, `phone`, `social_links`, `maps_embed`, `seo_title`, `seo_description`, `announcement_enabled`, `announcement_text`, `announcement_bg_color`) VALUES\n"
        "(1, 'Title Here', 'Walk-ins welcome. Appointments preferred. Edit this copy in Admin → Page Content.', 'tenant_1/43cd823af3aa4ad2_1784755944.jpg', 'Book Now', '#book', 'Company Name Here', 'Address Here', '(555) 000-0000', '{\\\"instagram\\\":\\\"\\\",\\\"facebook\\\":\\\"\\\",\\\"tiktok\\\":\\\"\\\"}', '', 'Company Name Here', 'Edit this description in Admin → Page Content.', 0, '', '#1c1410');\n",
    )

    sql = replace_insert_block(
        sql,
        "reminder_settings",
        "INSERT INTO `reminder_settings` (`tenant_id`, `email_enabled`, `sms_enabled`, `hours_before`, `email_from_name`, `email_from_address`, `email_subject`, `sms_template`, `email_template`) VALUES\n"
        "(1, 1, 0, 24, 'Company Name Here', NULL, 'Reminder: your appointment at Company Name Here', 'Hi {name}, reminder: {service} on {date} at {time} with {barber}. Reply STOP to opt out.', 'Hi {name},\\r\\n\\r\\nThis is a reminder that you have {service} on {date} at {time} with {barber} at {shop}.\\r\\n\\r\\nSee you soon!');\n",
    )

    sql = replace_insert_block(
        sql,
        "license_metrics",
        "INSERT INTO `license_metrics` (`tenant_id`, `subscription_type`, `renewal_date`, `sms_count_month`, `email_count_month`, `booking_count_month`, `notes`) VALUES\n"
        "(1, 'hosted_subdomain', DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 0, 0, 0, 'Test sale: hosted subdomain charlies.barber-shop.us');\n",
    )

    sql = replace_insert_block(
        sql,
        "payment_settings",
        "INSERT INTO `payment_settings` (`tenant_id`, `mode`, `enable_stripe`, `enable_paypal`, `enable_venmo`, `enable_square`, `enable_ach`, `enable_pay_at_shop`, `stripe_publishable_key`, `stripe_secret_key`, `stripe_webhook_secret`, `paypal_client_id`, `paypal_client_secret`, `paypal_webhook_id`, `square_application_id`, `square_access_token`, `square_location_id`, `default_currency`, `booking_payments`, `retail_payments`, `updated_at`) VALUES\n"
        "(1, 'test', 0, 0, 0, 0, 0, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'USD', 'off', 'optional', CURRENT_TIMESTAMP);\n",
    )

    sql = replace_insert_block(sql, "users", USERS_INSERT)
    sql = replace_insert_block(sql, "bookings", BOOKINGS_INSERT)

    # Drop PII / leftover demo transactions
    sql = replace_insert_block(
        sql,
        "booking_reminders",
        "-- no pending reminder rows in a fresh hosted shop\n",
    )
    sql = replace_insert_block(
        sql,
        "shop_orders",
        "-- no retail orders in a fresh hosted shop\n",
    )
    sql = replace_insert_block(
        sql,
        "shop_order_items",
        "-- no retail order items in a fresh hosted shop\n",
    )
    sql = replace_insert_block(
        sql,
        "payments",
        "-- no payments in a fresh hosted shop\n",
    )

    sql = replace_insert_block(
        sql,
        "schema_migrations",
        "INSERT INTO `schema_migrations` (`filename`, `applied_at`) VALUES\n"
        "('001_schema.sql', CURRENT_TIMESTAMP),\n"
        "('002_bookings.sql', CURRENT_TIMESTAMP),\n"
        "('003_users_reminders.sql', CURRENT_TIMESTAMP),\n"
        "('004_products.sql', CURRENT_TIMESTAMP),\n"
        "('005_sms_carriers.sql', CURRENT_TIMESTAMP),\n"
        "('006_client_portal.sql', CURRENT_TIMESTAMP),\n"
        "('007_payments.sql', CURRENT_TIMESTAMP),\n"
        "('008_staff_time_off.sql', CURRENT_TIMESTAMP),\n"
        "('009_demo_presence.sql', CURRENT_TIMESTAMP),\n"
        "('010_user_roles.sql', CURRENT_TIMESTAMP);\n",
    )

    sql = sql.replace(
        "ALTER TABLE `users`\n"
        "  ADD PRIMARY KEY (`id`),\n"
        "  ADD KEY `idx_users_tenant_phone` (`tenant_id`,`phone`),\n"
        "  ADD KEY `idx_users_tenant_email` (`tenant_id`,`email`),\n"
        "  ADD KEY `idx_users_login_token` (`login_token`);",
        "ALTER TABLE `users`\n"
        "  ADD PRIMARY KEY (`id`),\n"
        "  ADD UNIQUE KEY `uq_users_tenant_username` (`tenant_id`,`username`),\n"
        "  ADD KEY `idx_users_tenant_phone` (`tenant_id`,`phone`),\n"
        "  ADD KEY `idx_users_tenant_email` (`tenant_id`,`email`),\n"
        "  ADD KEY `idx_users_login_token` (`login_token`);",
    )

    sql = sql.replace(
        "ALTER TABLE `bookings`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;",
        "ALTER TABLE `bookings`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;",
    )
    sql = sql.replace(
        "ALTER TABLE `booking_reminders`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;",
        "ALTER TABLE `booking_reminders`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;",
    )
    sql = sql.replace(
        "ALTER TABLE `users`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;",
        "ALTER TABLE `users`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;",
    )
    sql = sql.replace(
        "ALTER TABLE `payments`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;",
        "ALTER TABLE `payments`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;",
    )
    sql = sql.replace(
        "ALTER TABLE `shop_orders`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;",
        "ALTER TABLE `shop_orders`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;",
    )
    sql = sql.replace(
        "ALTER TABLE `shop_order_items`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;",
        "ALTER TABLE `shop_order_items`\n  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;",
    )

    if "Classic Cuts" in sql or "Your BarberShop demo" in sql:
        raise SystemExit("Classic Cuts / invalid subdomain still present in generated SQL")

    marker = "START TRANSACTION;\n"
    if marker not in sql:
        raise SystemExit("Could not find START TRANSACTION in dump")
    sql = sql.replace(marker, marker + DROPS, 1)

    DEST.parent.mkdir(parents=True, exist_ok=True)
    DEST.write_text(HEADER + sql, encoding="utf-8")
    print(f"Wrote {DEST} ({DEST.stat().st_size} bytes)")


if __name__ == "__main__":
    main()
