package edu.neusoft.project.model.dao;

import edu.neusoft.project.model.vo.Person;
import edu.neusoft.project.model.vo.Song;
import edu.neusoft.project.util.DBconn;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Repository
public class SongDao {
    private Connection conn;
    private PreparedStatement pst=null;
    private ResultSet rs=null;


    public Song selectByName(String s) {
        String sql1="SELECT * FROM songlist WHERE name LIKE concat('%',?,'%') ORDER BY orderby";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql1);
            pst.setString(1,s);
            System.out.println(pst);
            rs=pst.executeQuery();
            while (rs.next()) {
                System.out.println();
//                Person p=new Person();
                Song song = new Song();
                System.out.println(rs);
                song.setId(rs.getString("id"));
                song.setName(rs.getString("name"));
                song.setSinger(rs.getString("singer"));
                song.setOrderby(rs.getInt("orderby"));

                if(song!=null){

                    return song;
                }
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return null;
    }

    public Song selectById(String searchText) {
        String sql2="SELECT * FROM songlist WHERE id LIKE concat('%',?,'%') ORDER BY orderby";
        conn= DBconn.getConn();

        try {
            pst=conn.prepareStatement(sql2);
            pst.setString(1,searchText);
            System.out.println(pst);
            rs=pst.executeQuery();
            System.out.println();
            while (rs.next()) {
//                System.out.println();
//                Person p=new Person();
                Song song = new Song();
                System.out.println(rs);
                song.setId(rs.getString("id"));
                song.setName(rs.getString("name"));
                song.setSinger(rs.getString("singer"));
                song.setOrderby(rs.getInt("orderby"));

                if(song!=null){

                    return song;
                }
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return null;
    }
    public Song selectByIdPrecise(String searchText) {
        String sql2="SELECT * FROM songlist WHERE id =? ORDER BY orderby limit 1 ";
        conn= DBconn.getConn();

        try {
            pst=conn.prepareStatement(sql2);
            pst.setString(1,searchText);
            System.out.println(pst);
            rs=pst.executeQuery();
            System.out.println();
            while (rs.next()) {
//                System.out.println();
//                Person p=new Person();
                Song song = new Song();
                System.out.println(rs);
                song.setId(rs.getString("id"));
                song.setName(rs.getString("name"));
                song.setSinger(rs.getString("singer"));
                song.setOrderby(rs.getInt("orderby"));

                if(song!=null){

                    return song;
                }
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return null;
    }
    //搜索后Listened添加1
    public void addListened(Person person) {
        String sql3="UPDATE userlist SET personListened =? WHERE personCode = ? ";
        person.setPersonListened(person.getPersonListened()+1);
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql3);
            pst.setInt(1,person.getPersonListened());
            pst.setString(2,person.getPersonCode());
            int rs=pst.executeUpdate();
            System.out.println(rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }

    }

    public int likeAct(String id, String personCode) {
        int ret=0;
        String sql = "INSERT INTO `music`.`mysong`(`personCode`, `songID`, `songName`) VALUES (?,? , (SELECT songlist.name from songlist where songlist.id = ?)) ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, personCode);
            pst.setString(2,id);
            pst.setString(3,id);
            ret =  pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }

        return ret;
    }

    public int unlikeAct(String id, String personCode) {
        int ret=0;
        String sql = "delete from `music`.`mysong` where `personCode` = ? and `songID` = ?  ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, personCode);
            pst.setString(2,id);
            ret =  pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }

        return ret;
    }

    public int insSong(int songid, String musicName, String musician, int commentCount) {
        int ret=0;
        String id = String.valueOf(songid);
        String count = String.valueOf(commentCount);
        String sql = "INSERT INTO `music`.`songlist`(`id`, `name`, `singer`, `orderby`) VALUES (?, ?, ?, ?); ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, id);
            pst.setString(2,musicName);
            pst.setString(3,musician);
            pst.setString(4,count);
            ret =  pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }

        return ret;
    }
}
