-- Example Customer Database
-- PostgreSQL-style schema and seed data based on the Customer Database Schema page.

CREATE TABLE customers (
  customer_id UUID PRIMARY KEY,
  customer_number VARCHAR(40) UNIQUE NOT NULL,
  first_name VARCHAR(100),
  middle_name VARCHAR(100),
  last_name VARCHAR(100),
  full_name VARCHAR(255),
  date_of_birth DATE,
  gender VARCHAR(50),
  profile_photo_url TEXT,
  email VARCHAR(255) UNIQUE NOT NULL,
  phone_mobile VARCHAR(40),
  phone_home VARCHAR(40),
  phone_work VARCHAR(40),
  preferred_language VARCHAR(20),
  preferred_currency VARCHAR(10),
  customer_status VARCHAR(40),
  customer_type VARCHAR(40),
  account_created_at TIMESTAMPTZ,
  last_login_at TIMESTAMPTZ,
  last_activity_at TIMESTAMPTZ,
  marketing_opt_in BOOLEAN DEFAULT FALSE,
  sms_opt_in BOOLEAN DEFAULT FALSE,
  email_verified BOOLEAN DEFAULT FALSE,
  phone_verified BOOLEAN DEFAULT FALSE,
  created_by UUID,
  updated_at TIMESTAMPTZ
);

CREATE TABLE customer_addresses (
  address_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  address_type VARCHAR(40),
  address_line_1 VARCHAR(255),
  address_line_2 VARCHAR(255),
  city VARCHAR(100),
  state_province VARCHAR(100),
  postal_code VARCHAR(30),
  country VARCHAR(100),
  latitude DECIMAL(10, 7),
  longitude DECIMAL(10, 7),
  is_primary BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ
);

CREATE TABLE customer_authentication (
  auth_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  username VARCHAR(100) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  password_salt TEXT NOT NULL,
  password_last_changed TIMESTAMPTZ,
  mfa_enabled BOOLEAN DEFAULT FALSE,
  mfa_secret TEXT,
  failed_login_attempts INTEGER DEFAULT 0,
  account_locked BOOLEAN DEFAULT FALSE,
  last_login_ip VARCHAR(45),
  device_fingerprint TEXT,
  session_token TEXT,
  refresh_token TEXT,
  last_password_reset TIMESTAMPTZ
);

CREATE TABLE customer_preferences (
  preference_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  theme_preference VARCHAR(40),
  notification_preferences JSONB,
  communication_channel VARCHAR(40),
  interests JSONB,
  personalization_data JSONB,
  accessibility_settings JSONB
);

CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  order_number VARCHAR(60) UNIQUE NOT NULL,
  order_status VARCHAR(40),
  order_total DECIMAL(12, 2),
  tax_amount DECIMAL(12, 2),
  shipping_amount DECIMAL(12, 2),
  discount_amount DECIMAL(12, 2),
  currency VARCHAR(10),
  payment_status VARCHAR(40),
  payment_method VARCHAR(60),
  billing_address_id UUID REFERENCES customer_addresses(address_id),
  shipping_address_id UUID REFERENCES customer_addresses(address_id),
  order_created_at TIMESTAMPTZ,
  fulfilled_at TIMESTAMPTZ
);

CREATE TABLE order_items (
  order_item_id UUID PRIMARY KEY,
  order_id UUID NOT NULL REFERENCES orders(order_id),
  product_id UUID,
  sku VARCHAR(80),
  product_name VARCHAR(255),
  quantity INTEGER,
  unit_price DECIMAL(12, 2),
  total_price DECIMAL(12, 2),
  tax_amount DECIMAL(12, 2)
);

CREATE TABLE payments (
  payment_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  order_id UUID REFERENCES orders(order_id),
  transaction_id VARCHAR(120),
  payment_method VARCHAR(60),
  payment_provider VARCHAR(80),
  amount DECIMAL(12, 2),
  currency VARCHAR(10),
  payment_status VARCHAR(40),
  authorization_code VARCHAR(80),
  ach_trace_number VARCHAR(120),
  invoice_number VARCHAR(80),
  payment_date TIMESTAMPTZ
);

CREATE TABLE support_tickets (
  ticket_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  subject VARCHAR(255),
  description TEXT,
  ticket_status VARCHAR(40),
  priority VARCHAR(40),
  assigned_agent UUID,
  resolution_notes TEXT,
  created_at TIMESTAMPTZ,
  resolved_at TIMESTAMPTZ
);

CREATE TABLE customer_communications (
  communication_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  communication_type VARCHAR(40),
  direction VARCHAR(40),
  subject VARCHAR(255),
  message_body TEXT,
  communication_status VARCHAR(40),
  sent_at TIMESTAMPTZ
);

CREATE TABLE customer_segments (
  segment_id UUID PRIMARY KEY,
  segment_name VARCHAR(120),
  description TEXT,
  rules_json JSONB
);

CREATE TABLE customer_segment_memberships (
  membership_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  segment_id UUID NOT NULL REFERENCES customer_segments(segment_id),
  joined_at TIMESTAMPTZ
);

CREATE TABLE customer_activity_log (
  activity_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  activity_type VARCHAR(80),
  page_url TEXT,
  device_type VARCHAR(40),
  browser VARCHAR(80),
  operating_system VARCHAR(80),
  ip_address VARCHAR(45),
  session_id VARCHAR(120),
  activity_timestamp TIMESTAMPTZ
);

CREATE TABLE subscriptions (
  subscription_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  plan_name VARCHAR(120),
  billing_cycle VARCHAR(40),
  start_date DATE,
  renewal_date DATE,
  cancellation_date DATE,
  subscription_status VARCHAR(40)
);

CREATE TABLE loyalty_accounts (
  loyalty_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  points_balance INTEGER,
  tier_level VARCHAR(40),
  lifetime_points INTEGER,
  last_activity TIMESTAMPTZ
);

CREATE TABLE audit_log (
  audit_id UUID PRIMARY KEY,
  entity_name VARCHAR(120),
  entity_id UUID,
  action_type VARCHAR(40),
  old_values JSONB,
  new_values JSONB,
  changed_by UUID,
  changed_at TIMESTAMPTZ
);

CREATE TABLE ai_customer_profile (
  ai_profile_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  churn_risk_score DECIMAL(5, 4),
  lifetime_value_score DECIMAL(12, 2),
  recommendation_profile JSONB,
  sentiment_score DECIMAL(5, 4),
  behavioral_cluster VARCHAR(80),
  engagement_score DECIMAL(5, 4)
);

INSERT INTO customers (
  customer_id, customer_number, first_name, middle_name, last_name, full_name,
  date_of_birth, gender, profile_photo_url, email, phone_mobile, phone_home,
  phone_work, preferred_language, preferred_currency, customer_status,
  customer_type, account_created_at, last_login_at, last_activity_at,
  marketing_opt_in, sms_opt_in, email_verified, phone_verified, created_by, updated_at
) VALUES
('11111111-1111-4111-8111-111111111111', 'CUST-10001', 'Avery', NULL, 'Johnson', 'Avery Johnson',
 '1988-04-12', 'Non-binary', 'https://example.com/profiles/avery.jpg', 'avery.johnson@example.com',
 '+1-415-555-0198', NULL, '+1-415-555-0100', 'en-US', 'USD', 'active', 'VIP',
 '2025-01-14 09:20:00+00', '2026-05-25 16:42:00+00', '2026-05-25 17:05:00+00',
 TRUE, TRUE, TRUE, TRUE, 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa', '2026-05-25 17:05:00+00'),
('22222222-2222-4222-8222-222222222222', 'CUST-10002', 'Maya', 'R.', 'Patel', 'Maya R. Patel',
 '1992-11-03', 'Female', 'https://example.com/profiles/maya.jpg', 'maya.patel@example.com',
 '+1-212-555-0171', NULL, NULL, 'en-US', 'USD', 'active', 'individual',
 '2025-07-08 13:00:00+00', '2026-05-20 10:14:00+00', '2026-05-20 10:33:00+00',
 TRUE, FALSE, TRUE, TRUE, 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa', '2026-05-20 10:33:00+00'),
('33333333-3333-4333-8333-333333333333', 'CUST-10003', 'Northstar', NULL, 'Labs', 'Northstar Labs',
 NULL, NULL, 'https://example.com/profiles/northstar.png', 'billing@northstarlabs.example',
 '+1-650-555-0144', NULL, '+1-650-555-0145', 'en-US', 'USD', 'active', 'business',
 '2024-10-30 18:10:00+00', '2026-05-28 19:02:00+00', '2026-05-28 20:11:00+00',
 TRUE, FALSE, TRUE, TRUE, 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa', '2026-05-28 20:11:00+00');

INSERT INTO customer_addresses VALUES
('aaaa1111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'shipping', '300 Market Street', 'Suite 1200', 'San Francisco', 'CA', '94105', 'United States', 37.7891000, -122.3942000, TRUE, '2025-01-14 09:24:00+00'),
('aaaa2222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 'home', '18 East 16th Street', 'Apt 8B', 'New York', 'NY', '10003', 'United States', 40.7369000, -73.9926000, TRUE, '2025-07-08 13:05:00+00'),
('aaaa3333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', 'billing', '900 Innovation Way', NULL, 'Palo Alto', 'CA', '94301', 'United States', 37.4419000, -122.1430000, TRUE, '2024-10-30 18:15:00+00');

INSERT INTO customer_authentication VALUES
('bbbb1111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'avery.johnson', '$argon2id$example_hash_avery', 'salt_avery', '2026-04-01 12:00:00+00', TRUE, 'mfa_secret_placeholder', 0, FALSE, '203.0.113.10', 'device_fp_avery_01', 'session_placeholder_avery', 'refresh_placeholder_avery', '2026-04-01 12:00:00+00'),
('bbbb2222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 'maya.patel', '$argon2id$example_hash_maya', 'salt_maya', '2026-03-15 15:40:00+00', FALSE, NULL, 1, FALSE, '198.51.100.21', 'device_fp_maya_01', 'session_placeholder_maya', 'refresh_placeholder_maya', NULL),
('bbbb3333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', 'northstar.admin', '$argon2id$example_hash_northstar', 'salt_northstar', '2026-05-01 09:15:00+00', TRUE, 'mfa_secret_placeholder', 0, FALSE, '192.0.2.35', 'device_fp_northstar_01', 'session_placeholder_northstar', 'refresh_placeholder_northstar', NULL);

INSERT INTO customer_preferences VALUES
('cccc1111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'dark', '{"email": true, "sms": true, "push": true}', 'email', '["automation", "analytics", "loyalty"]', '{"homepage_layout": "executive", "recommended_category": "workflow"}', '{"reduced_motion": false, "font_scale": "standard"}'),
('cccc2222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 'light', '{"email": true, "sms": false, "push": false}', 'email', '["subscriptions", "mobile"]', '{"homepage_layout": "compact", "recommended_category": "starter"}', '{"reduced_motion": true, "font_scale": "large"}'),
('cccc3333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', 'dark', '{"email": true, "sms": false, "push": true}', 'phone', '["enterprise", "security", "integrations"]', '{"homepage_layout": "admin", "recommended_category": "enterprise"}', '{"reduced_motion": false, "font_scale": "standard"}');

INSERT INTO orders VALUES
('dddd1111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'ORD-90001', 'completed', 299.00, 23.92, 0.00, 20.00, 'USD', 'paid', 'Visa', 'aaaa1111-1111-4111-8111-111111111111', 'aaaa1111-1111-4111-8111-111111111111', '2026-05-10 14:20:00+00', '2026-05-10 14:25:00+00'),
('dddd2222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 'ORD-90002', 'completed', 49.00, 3.92, 0.00, 0.00, 'USD', 'paid', 'PayPal', 'aaaa2222-2222-4222-8222-222222222222', 'aaaa2222-2222-4222-8222-222222222222', '2026-05-18 09:10:00+00', '2026-05-18 09:12:00+00'),
('dddd3333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', 'ORD-90003', 'pending', 2499.00, 199.92, 0.00, 250.00, 'USD', 'pending', 'ACH', 'aaaa3333-3333-4333-8333-333333333333', 'aaaa3333-3333-4333-8333-333333333333', '2026-05-28 18:45:00+00', NULL);

INSERT INTO order_items VALUES
('eeee1111-1111-4111-8111-111111111111', 'dddd1111-1111-4111-8111-111111111111', '99991111-1111-4111-8111-111111111111', 'AI-PRO-MONTH', 'AI Pro Monthly Plan', 1, 299.00, 299.00, 23.92),
('eeee2222-2222-4222-8222-222222222222', 'dddd2222-2222-4222-8222-222222222222', '99992222-2222-4222-8222-222222222222', 'AI-STARTER', 'AI Starter Plan', 1, 49.00, 49.00, 3.92),
('eeee3333-3333-4333-8333-333333333333', 'dddd3333-3333-4333-8333-333333333333', '99993333-3333-4333-8333-333333333333', 'AI-ENTERPRISE', 'AI Enterprise Implementation', 1, 2499.00, 2499.00, 199.92);

INSERT INTO payments VALUES
('ffff1111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'dddd1111-1111-4111-8111-111111111111', 'txn_10001', 'Visa', 'Stripe', 299.00, 'USD', 'completed', 'AUTH10001', NULL, 'INV-10001', '2026-05-10 14:21:00+00'),
('ffff2222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 'dddd2222-2222-4222-8222-222222222222', 'pp_10002', 'PayPal', 'PayPal', 49.00, 'USD', 'completed', 'AUTH10002', NULL, 'INV-10002', '2026-05-18 09:11:00+00'),
('ffff3333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', 'dddd3333-3333-4333-8333-333333333333', 'ach_10003', 'ACH', 'Stripe', 2499.00, 'USD', 'pending', NULL, 'TRACE10003', 'INV-10003', '2026-05-28 18:46:00+00');

INSERT INTO support_tickets VALUES
('12121111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'Workflow export question', 'Customer asked how to export workflow run history.', 'closed', 'medium', 'bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb', 'Sent export instructions and API endpoint reference.', '2026-05-12 11:00:00+00', '2026-05-12 12:05:00+00'),
('12122222-2222-4222-8222-222222222222', '33333333-3333-4333-8333-333333333333', 'Enterprise SSO setup', 'Northstar Labs requested SAML metadata validation.', 'open', 'high', 'cccccccc-cccc-4ccc-8ccc-cccccccccccc', NULL, '2026-05-29 15:30:00+00', NULL);

INSERT INTO customer_communications VALUES
('13131111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'email', 'outgoing', 'Your workflow export guide', 'Here are the steps to export workflow history.', 'delivered', '2026-05-12 12:02:00+00'),
('13132222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 'sms', 'outgoing', 'Payment receipt', 'Your payment for AI Starter has been received.', 'delivered', '2026-05-18 09:13:00+00');

INSERT INTO customer_segments VALUES
('14141111-1111-4111-8111-111111111111', 'VIP Customers', 'High-value customers with strong engagement.', '{"customer_type": "VIP", "engagement_score_gte": 0.80}'),
('14142222-2222-4222-8222-222222222222', 'Enterprise Leads', 'Business customers evaluating enterprise plans.', '{"customer_type": "business", "plan_interest": "enterprise"}');

INSERT INTO customer_segment_memberships VALUES
('15151111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', '14141111-1111-4111-8111-111111111111', '2026-05-10 15:00:00+00'),
('15153333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', '14142222-2222-4222-8222-222222222222', '2026-05-28 19:00:00+00');

INSERT INTO customer_activity_log VALUES
('16161111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'purchase', '/checkout/success', 'desktop', 'Chrome', 'Windows', '203.0.113.10', 'sess_avery_001', '2026-05-10 14:22:00+00'),
('16162222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 'login', '/dashboard', 'mobile', 'Safari', 'iOS', '198.51.100.21', 'sess_maya_001', '2026-05-20 10:14:00+00'),
('16163333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', 'pricing_view', '/pricing', 'desktop', 'Edge', 'Windows', '192.0.2.35', 'sess_northstar_001', '2026-05-28 18:40:00+00');

INSERT INTO subscriptions VALUES
('17171111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 'Pro', 'monthly', '2026-05-10', '2026-06-10', NULL, 'active'),
('17172222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 'Starter', 'monthly', '2026-05-18', '2026-06-18', NULL, 'active'),
('17173333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', 'Enterprise', 'yearly', '2026-06-01', '2027-06-01', NULL, 'pending');

INSERT INTO loyalty_accounts VALUES
('18181111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 4200, 'Gold', 12800, '2026-05-25 17:05:00+00'),
('18182222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 650, 'Silver', 650, '2026-05-20 10:33:00+00');

INSERT INTO audit_log VALUES
('19191111-1111-4111-8111-111111111111', 'customers', '11111111-1111-4111-8111-111111111111', 'update', '{"customer_status": "trial"}', '{"customer_status": "active"}', 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa', '2026-05-10 14:30:00+00'),
('19192222-2222-4222-8222-222222222222', 'subscriptions', '17173333-3333-4333-8333-333333333333', 'insert', '{}', '{"subscription_status": "pending"}', 'aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa', '2026-05-28 18:47:00+00');

INSERT INTO ai_customer_profile VALUES
('20201111-1111-4111-8111-111111111111', '11111111-1111-4111-8111-111111111111', 0.0800, 14500.00, '{"recommended_products": ["enterprise-workflows", "analytics-pack"]}', 0.9200, 'power_user', 0.9600),
('20202222-2222-4222-8222-222222222222', '22222222-2222-4222-8222-222222222222', 0.2100, 1850.00, '{"recommended_products": ["starter-automation", "mobile-notifications"]}', 0.7400, 'starter_growth', 0.6800),
('20203333-3333-4333-8333-333333333333', '33333333-3333-4333-8333-333333333333', 0.1200, 88000.00, '{"recommended_products": ["sso", "dedicated-support", "audit-pack"]}', 0.8600, 'enterprise_eval', 0.8900);