# Conexión local
psql -h localhost -U lacajita_app -d lacajita_db

# Variables de entorno
PG_HOST=localhost
PG_PORT=5432
PG_DB=lacajita_db
PG_USER=lacajita_app

-- Playlists
CREATE TABLE lacajita_playlists (
  id TEXT PRIMARY KEY,
  segment_id INT,
  title TEXT NOT NULL,
  description TEXT,
  category TEXT,
  img TEXT,                    -- URL de imagen
  active INT DEFAULT 1,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Canales LiveTV
CREATE TABLE livetv_channels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(200) NOT NULL,
  stream_url TEXT NOT NULL,
  logo_url TEXT,               -- Logo principal
  logo_thumbnail TEXT,         -- Miniatura
  quality VARCHAR(20) DEFAULT 'HD',
  category VARCHAR(100),
  is_active BOOLEAN DEFAULT true,
  display_order INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Noticias
CREATE TABLE news_feed (
  id SERIAL PRIMARY KEY,
  title VARCHAR(500) NOT NULL,
  content TEXT,
  image_url TEXT,
  author_id UUID REFERENCES users(id),
  published_at TIMESTAMPTZ,
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Usuarios
CREATE TABLE users (
  id UUID PRIMARY KEY,
  auth0_id TEXT UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255),
  role VARCHAR(50) DEFAULT 'user',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Roles: admin, producer, agency, user

-- Solicitudes de anuncios
CREATE TABLE ad_requests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agency_id UUID REFERENCES users(id),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  budget DECIMAL(10,2),
  status VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Estados: pending, approved, rejected, active, completed

/opt/adm-caja-unified/db-migration/
├── schema_postgres.sql           # Esquema base
├── livetv_native_system.sql      # Sistema LiveTV
├── news_interactions.sql         # Interacciones noticias
├── ads_pricing_agencies.sql      # Sistema de anuncios
├── invoice_system.sql            # Facturación
└── performance_indices.sql       # Índices de rendimiento

cd /opt/adm-caja-unified/db-migration

# Ejecutar archivo SQL
psql -h localhost -U lacajita_app -d lacajita_db -f schema_postgres.sql

# Con script Python
python3 migrate_apis_to_postgres.py

SELECT id, title, category, img
FROM lacajita_playlists
WHERE active = 1
ORDER BY created_at DESC
LIMIT 20;

SELECT 
  id, name, stream_url, logo_url, logo_thumbnail,
  quality, category
FROM livetv_channels
WHERE is_active = true
ORDER BY display_order ASC;

SELECT 
  n.id, n.title, n.content, n.image_url,
  u.name as author_name, n.published_at
FROM news_feed n
JOIN users u ON n.author_id = u.id
WHERE n.is_published = true
ORDER BY n.published_at DESC
LIMIT 10;

SELECT 
  'playlists' as type, COUNT(*) as total 
FROM lacajita_playlists WHERE active = 1
UNION ALL
SELECT 
  'channels' as type, COUNT(*) as total 
FROM livetv_channels WHERE is_active = true
UNION ALL
SELECT 
  'news' as type, COUNT(*) as total 
FROM news_feed WHERE is_published = true;

-- Índices de rendimiento
CREATE INDEX idx_playlists_active ON lacajita_playlists(active);
CREATE INDEX idx_playlists_category ON lacajita_playlists(category);
CREATE INDEX idx_channels_active ON livetv_channels(is_active);
CREATE INDEX idx_channels_order ON livetv_channels(display_order);
CREATE INDEX idx_news_published ON news_feed(is_published, published_at DESC);
CREATE INDEX idx_users_auth0 ON users(auth0_id);

# Backup completo
pg_dump -h localhost -U lacajita_app -d lacajita_db > backup_$(date +%Y%m%d).sql

# Backup solo datos
pg_dump -h localhost -U lacajita_app -d lacajita_db --data-only > data_backup.sql

# Restore
psql -h localhost -U lacajita_app -d lacajita_db < backup.sql